BLOG DOS
INCOMPARÁVEIS

Fala, pessoal! Cansou daquela tabela padrão de DRE na sua empresa? Quer aprimorar seus conhecimentos em DAX e construir uma matriz inteligente no Power Bi de forma que você consiga comparar o realizado e orçado mês a mês e também expandir os níveis das contas? Hoje mostraremos como fazer isso e muito mais!

Nosso objetivo final será chegar nessa página:

Clique Aqui para dominar o Power BI, e se tornar um profissional valioso para o mercado de trabalho.

Dados de entrada

Temos um arquivo em Excel composto pelas seguintes abas/tabelas:

1. Aba Plano de Contas (dPlanoContas): contém a estruturação do plano de contas da empresa, com nome da conta, código contábil e nível das contas (hierarquias/agrupamentos). Note que a coluna cdConta possui valores distintos.

2. Aba Realizado (fRealizado): possui o valor realizado para cada cdConta e Data. Veja que a granularidade é diária e os valores da coluna cdConta se repetem várias vezes.

3. Aba Orçado (fOrçado): contém os valores de orçamento para cada cdConta e Mês/ano. Nessa tabela a granularidade é mensal e os valores de cdConta também se repetem em várias linhas.

4. Aba Máscara DRE (dPlanoGerencial): possui o Nível 1 das contas bem como a classificação Subtotal. Esta, quando se tratar de uma conta que é resultado da soma de outras, aparecerá o valor 1, caso contrário aparecerá o valor 0. Veja que também há uma coluna Ordem e ela é responsável por indicar o ordenamento correto das contas. – sem isso, nossa soma acumulada linha a linha poderá ter problemas. Daqui a pouco você descobrirá que ela será fundamental na nossa medida DAX.

Veja na figura acima que a nossa ideia é poder somar os valores das linhas acima de uma conta que é subtotal (Subtotal = 1). Veja que a linha GROSS PROFIT deve ser o resultado da soma de NET REVENUES, COSTS OF GOOD SALES e LOGISTISC. E assim sucessivamente até o final da tabela, beleza?!

Importando as tabelas

Para importar as tabelas do nosso arquivo em Excel, basta ir Página Inicial → Obter Dados → Excel → Selecione o arquivo.

Assim que clicar para abrir aparecerá uma janela para você selecionar as Abas / Tabelas de interesse como essa daqui:

Você deve estar estranhando… Por que apareceram mais dados que aquelas 4 abas que mostramos quando falamos de cada aba do arquivo de entrada? É que os dados estavam formatados como Tabela no Excel, então o Power Bi mostra a lista de Abas e de Tabelas (mesmo que, na prática, elas possuam a mesma informação), entendeu?! Vamos selecionar as tabelas destacadas em vermelho na figura, ok?!

Precisamos relacionar a tabela dPlano Gerencial com dPlanoContas através da coluna Nível 1. Mas veja que a chance de possuir alguma linha escrita de forma diferente na tabela dPlanoGerencial (nossa máscara DRE) é bem grande, afinal essa máscara dificilmente será um arquivo que você exportará de um sistema (ou seja, deverá ser criada por você) – no nosso exemplo ela foi criada manualmente. Vamos precisar fazer o nosso ETL (Extract, Transform, Load)!

ETL

Podemos diminuir a chance de erros em relacionamentos entre as colunas Nível 1 das tabelas dPlanoGerencial e dPlanoContas separando o código do texto, em ambas as tabelas. Faremos isso adicionando uma nova coluna.

Como essa ação é um padrão “fácil” do Power Bi identificar, então usaremos a Coluna de Exemplos para extrair a parte textual da coluna Nível 1. Veja:

Etapas:
Selecione a coluna Nível 1 na tabela dPlanoGerencial → Adicionar Coluna →   Coluna de Exemplos → Da Seleção → Digite o valor que se deseja extrair (no nosso caso é o texto) → Se o autopreenchimento foi realizado corretamente, clique Ok → Renomeie para Conta Gerencial a Coluna 1 → Enter.

Agora vamos transformar a coluna Nível 1 de forma que só fique a parte numérica nela (código) e vemos que o que separa o código do nome da conta (texto) é um ponto seguido de um espaço em branco. Veja:

Etapas:
Selecione a coluna Nível 1 na tabela dPlanoGerencial → Transformar → Extrair → Texto Antes do Delimitador → Delimitador → Digite um ponto seguido de um espaço em branco → Ok.

Na tabela dPlanoContas só faremos a transformação na própria coluna, isto é, não precisaremos dos nomes porque já temos isso na tabela dPlanoGerencial (relacionaremos ambas depois através do Nível 1). Da mesma forma que fizemos na figura 8, seguiremos as etapas abaixo:

Etapas:
Selecione a coluna Nível 1 na tabela dPlanoContas → Transformar → Extrair → Texto Antes do Delimitador → Delimitador → Digite um ponto seguido de um espaço em branco → Ok.

Se, após verificar os tipos de dados nas tabelas, estiver tudo certo (data como Date, valores numéricos com Decimal, etc), clique em Fechar e Aplicar.

Relacionamentos

Assim que a aplicar as transformações, você verá no modelo que alguns relacionamentos foram identificados automaticamente no Power Bi e esse modelo é do tipo Snowflake, veja:

Medidas DAX

Construindo a dCalendário com DAX

Sabemos que em todo modelo é importante ter uma tabela de calendário. Faremos essa tabela ser dinâmica, isto é, ter uma data de início e fim com base nos nossos dados de entrada usando DAX.

Para criar uma tabela em DAX, basta ir em Modelagem, clicar no botão abaixo em destaque e digitar a seguinte fórmula:

dCalendario =
VAR varDataMinFato =
    MIN ( fRealizado[Data] )
VAR varDataMaxFato =
    MAX ( fRealizado[Data] )
VAR varAnoMin =
    YEAR ( varDataMinFato )
VAR varAnoMax =
    YEAR ( varDataMaxFato )
VAR varDataMin =
    DATE ( varAnoMin; 01; 01 )
VAR varDataMax =
    DATE ( varAnoMax; 12; 31 )
RETURN
    CALENDAR ( varDataMin; varDataMax )

Após dar o enter na fórmula acima, você notará que será criada na tabela dCalendario uma coluna chamada Date, com a lista de datas compreendidas entre varDataMin e varDataMax. Lembre-se de mudar a formatação dessa coluna (dd/mm/yyyy) e renomeie essa coluna para Data.

Caso não esteja aparecendo Ferramentas da tabela e Ferramentas de coluna na Faixa de Opções do Power Bi, você precisará ativá-las assim:

Etapas:
(1) File → (2) Opções e Configurações → (3) Opções → (4) Recursos de visualização →  (5) Marque Faixa de opções atualizada → (6) Ok.

Agora, vamos adicionar as colunas de Mês, Ano e Nome do Mês. Ainda com a tabela dCalendario selecionada, clique em Ferramenta da tabela → Nova Coluna e digite as três fórmulas a seguir, uma para cada coluna:

Mês =
MONTH ( dCalendario[Data] )

Ano =
YEAR ( dCalendario[Data] )

Nome do Mês =
FORMAT ( dCalendario[Data]; “mmmm” )

Por fim, vamos ordenar a coluna Nome do Mês de acordo com o número do mês (coluna Mês) da seguinte forma:

Etapas: 
Selecione a coluna Nome do Mês → Classificar por → Mês

Voltando para o Modelo, precisamos conectar a dCalendario e fazer os devidos relacionamentos. Basta arrastar as colunas Data entre a dCalendario e cada tabela fato (fOrçado e fRealizado), veja:

Clique Aqui para se tornar um expert em DAX aprendendo na prática diversas análises avançadas.

Calculando o valor Realizado

Nossa primeira medida será o total Realizado:

Quando utilizamos uma matriz para visualizar a medida criada (Realizado) junto da coluna Contas Gerenciais (da tabela dPlanoGerencial), vemos que o ordenamento das contas não parece estar correto, veja:

Para corrigir isso, vamos ordenar a coluna Conta Gerencial através da coluna Ordem.

Note que, apesar de termos conseguido ordenar corretamente as contas, aquelas contas que são subtotais não vieram (GROSS PROFIT e EBITDA não estão na nossa matriz – à esquerda):

Vamos mudar esse visual para Tabela rapidamente para conseguir ver as contas que faltaram (adicione a coluna Subtotal na tabela, no campo Valores):

Veja que ainda não aparecem as linhas de contas com Subtotal igual a 1. Precisamos clicar na seta referente à coluna que está em Valores desse visual (Conta Gerencial) e selecionar a opção Mostrar itens sem dados, veja:

Veja que agora apareceram aquelas contas com Subtotal igual a 1.

Agora vamos precisar preencher essas linhas com valores em branco. Antes disso, arraste a coluna Ordem para que ela apareça na tabela também. Assim que arrastá-la, lembre se clicar com o botão direito na setinha da coluna Ordem (em Valores do visual selecionado) e selecionar Não resumir:

O resultado será essa tabela:

Agora veremos como será importante essa coluna Ordem. Observe que a linha EBITDA é uma linha totalizadora (Subtotal = 1) e portanto seu valor deve corresponder à soma de todas as linhas acima dela. Essas linhas acima dela possuem o valor de Ordem inferior a ela, certo?! Sim! Então basta somar todas as linhas da tabela até chegar na linha 14? Não!

Além disso, precisamos levar em consideração a coluna Subtotal também. A soma das linhas anteriores deve ocorrer somente até chegar na primeira conta com Subtotal = 1. Ou seja, nesse exemplo vamos somar os valores das linhas que possuem Ordem entre 4 e 13 (4 <= Ordem < 13). Ou podemos também somar todas as linhas antes da linha 14, exceto as linhas com Subtotal = 1. Essa segunda opção parece ser mais fácil, certo?! Vamos por partes…

Primeiro, acumularemos os valores de cada linha. Veja:

Realizado Subtotais =
VAR varOrdemContexto =
    MAX ( dPlanoGerencial[Ordem] )
RETURN
    CALCULATE (
        SUM ( fRealizado[Valor] );
        FILTER ( ALL ( dPlanoGerencial ); dPlanoGerencial[Ordem] <= varOrdemContexto )
    )

Com essa fórmula veremos o seguinte resultado:

Ah, uma observação! Você poderia imaginar que ao colocarmos MAX( dPlanoGerencial [Ordem] ) em varOrderContexto, teríamos como resultado o total da ordem da tabela toda (= 23), mas lembre-se que a fórmula será aplicada no contexto em que ela estiver (não estamos usando a Calculate para alterar seu contexto, por exemplo). Então, em cada linha o valor de varOrderContexto será o valor máximo do contexto em que se está realizando o cálculo. Entendido?!

Bom, voltando ao que estávamos fazendo, veja que temos duas medidas: Realizado e Realizado Subtotal e agora precisamos uní-las numa só, ou seja, precisamos preencher os campos vazios sem valor da medida Realizado com o valor da medida Realizado Subtotal. A lógica é: sempre que tiver no nível de Subtotal, vou usar a medida Realizado Subtotal, caso contrário vou usar a medida Realizado.

Realizado =
VAR varSubtotal =
    SELECTEDVALUE ( dPlanoGerencial[Subtotal] )
VAR varOrdemContexto =
    MAX ( dPlanoGerencial[Ordem] )
VAR varValor =
    SUM ( fRealizado[Valor] )
VAR varValorSubtotal =
    CALCULATE (
        SUM ( fRealizado[Valor] );
        FILTER ( ALL ( dPlanoGerencial ); dPlanoGerencial[Ordem] <= varOrdemContexto )
    )
RETURN
    SWITCH (
        TRUE ();
        varSubtotal = 0; varValor;
        varSubtotal = 1
            && NOT ( ISINSCOPE ( dPlanoContas[Nível 2] ) ); varValorSubtotal;
        BLANK ()
    )

Veja como fico na nossa tabela após adicionar Realizado Correto em Valores:

Voltando para nosso visual Matriz deixaremos Conta Gerencial e Realizado Correto. Adicionaremos o Nível 2 nas Linhas para ativar o Drill Down. Lembre-se de clicar na seta ao lado de Conta Gerencial para remover as linhas em branco (Others), veja:

Mas temos um probleminha… Quando expandimos o GROSS PROFIT (que é uma conta subtotal), vemos que aparecem todas as contas que já tinham em outras aberturas mas deveria aparecer somente a própria linha, veja só:

Vamos resolver isso utilizando a função ISINSCOPE, veja:

EstaNoContexto =
ISINSCOPE ( dPlanoContas[Nível 2] )

Agora, vamos adicionar essa função na medida Realizado Correto.

Realizado Correto =
VAR varSubtotal =
    SELECTEDVALUE ( dPlanoGerencial[Subtotal] )
RETURN
    SWITCH (
        TRUE ();
        varSubtotal = 0; [Realizado];
        varSubtotal = 1
            && NOT ( ISINSCOPE ( dPlanoContas[Nível 2] ) ); [Realizado Subtotais];
        BLANK ()
    )

Pronto! Agora não teremos a abertura no Drill Down do Nível 2 em contas que são subtotais.

Vamos mover todas as medidas com cálculo do Realizado para uma só (chamaremos de Realizado)! No final, lembre-se de deletar aquelas medidas antigas que não usaremos mais (porque transformamos elas em variáveis)! Veja como ficou nossa medida Realizado no final:

Realizado =
VAR varSubtotal =
    SELECTEDVALUE ( dPlanoGerencial[Subtotal] )
VAR varOrdemContexto =
    MAX ( dPlanoGerencial[Ordem] )
VAR varValor =
    SUM ( fRealizado[Valor] )
VAR varValorSubtotal =
    CALCULATE (
        SUM ( fRealizado[Valor] ),
        FILTER ( ALL ( dPlanoGerencial ), dPlanoGerencial[Ordem] <= varOrdemContexto )
    )
RETURN
    SWITCH (
        TRUE (),
        varSubtotal = 0, varValor,
        varSubtotal = 1
            && NOT ( ISINSCOPE ( dPlanoContas[Nível 2] ) ), varValorSubtotal,
        BLANK ()
    )

Calculando o valor Orçado

Vamos aproveitar e já criar a medida Orçado. É exatamente a mesma coisa que a Realizado, mas precisamos realizar a mudança apenas onde aparece o nome da tabela fRealizado (trocar para fOrçado). Ficou assim:

Orçado =
VAR varSubtotal =
    SELECTEDVALUE ( dPlanoGerencial[Subtotal] )
VAR varOrdemContexto =
    MAX ( dPlanoGerencial[Ordem] )
VAR varValor =
    SUM ( fOrcado[Valor] )
VAR varValorSubtotal =
    CALCULATE (
        SUM ( fOrcado[Valor] );
        FILTER ( ALL ( dPlanoGerencial ); dPlanoGerencial[Ordem] <= varOrdemContexto )
    )
RETURN
    SWITCH (
        TRUE ();
        varSubtotal = 0; varValor;
        varSubtotal = 1
            && NOT ( ISINSCOPE ( dPlanoContas[Nível 2] ) ); varValorSubtotal;
        BLANK ()
    )

Calculando o Desvio

O desvio será a diferença entre o Realizado e o Orçado, certo?! Essa é mole!

Desvio =
[Realizado] – [Orçado]

Já o desvio percentual será calculado utilizando a função DIVIDE assim:

Desvio % =
DIVIDE ( [Desvio]; ABS ( [Orçado] ) )

A função ABS serve para deixar o valor Orçado absoluto (sinal positivo).

Visuais

Para chegarmos naquela página que mostrei no início do artigo precisamos adicionar alguns ícones na nossa coluna Desvio % da matriz. Para isso, basta seguir:

Formatação:
Formato → Formatação condicional → Seleção da medida Desvio % no dropdown → Ativar Ícones → Controles Avançados → Configurar os ícones conforme imagem (desvio negativo será vermelho e positivo será verde).

Ah, vamos adicionar também a coluna Descrição da Conta da tabela dPlanoContas nas linhas da matriz. Dê duplo clique para renomeá-la para Conta Contábil. Adicione também Nome do Mês nas colunas.

Para finalizar, vamos adicionar um filtro para escolha do mês, veja:

Finalmente, chegamos ao visual que queríamos, após formatar a tabela, adicionar um título na página e mudar o background… Veja como ficou:

Viu? Com apenas 4 medidas conseguimos montar nossa Matriz DRE com Drill Down de Níveis de Contas, ícones com base no valor % do desvio e totalmente dinâmica e que também é responsiva ao filtro de mês e demais seleções! Incrível, né?!

Ficamos por aqui, pessoal! Espero que tenham gostado do conteúdo de hoje! Ele foi baseado na Live 3 Workshop #1 Power BI – Dashboard Demonstrativo de Resultado (DRE Contábil) com DAX Avançado.

Abraços,
Leonardo.