BLOG DOS
INCOMPARÁVEIS

Fala galera, tudo bem??

Hoje nosso post é referente a Live #33 e vou mostrar as diferenças dos cálculos realizados no Excel e no Power BI. Não vou mostrar somente as fórmulas que são equivalentes, vou ensinar nos detalhes como é feito o cálculo por trás das fórmulas em DAX.

A ideia aqui é desmistificar e esclarecer para o pessoal essas questões! As principais funções que iremos analisar hoje são CALCULATE, VALUES e ALL que normalmente são usadas para alterar o contexto de avaliação dos cálculos.

Então, me acompanha até o final que vai ficar craque em DAX e não vai mais precisar ficar chutando como criar suas medidas até acertar o valor correto!

Base de dados e cálculos no Excel

Para exemplificar e fixar todas as etapas a gente vai usar uma base de dados do Excel, e nela iremos realizar todas as contas que depois serão feitas também no Power BI:

Figura 1: Base de dados

São duas tabelas relativamente simples, com uma contendo as informações dos produtos (nosso cadastro de produtos – tabela dimensão) e o outra contendo as informações das vendas (ocorrência de venda – tabela fato).

Na forma que está distribuída as tabelas não estão explicitamente definidas no Excel, vamos fazer isso pois ajuda bastante na organização:

Veja que na tabela de vendas temos a quantidade de venda do produto, porém precisamos das informações de preço unitário e do custo de cada produto! Assim, teremos pelo menos mais duas colunas nessa tabela. Bom, você que é fera no Excel….Qual a função que podemos utilizar para trazer essa informação para a tabela de vendas? Acredito que você acertou… é a famosa PROCX.

Vamos criar a célula da primeira coluna de valor unitário para nossa tabela de vendas com a PROCX.

=PROCX([@idProduto];Tabela1[idProduto];Tabela1[ValorUnitario])

Figura 2: Utilização PROCX

Automaticamente o Excel preenche as outras linha da coluna criada, pois formatamos como tabela nossos dados.

Podemos também trazer a informação de quantidade comprada de forma agregada dos produtos da tabela fato para tabela dimensão usando o SOMASE:

=SOMASE(Tabela2[idProduto];[@idProduto];Tabela2[Quantidade])

Figura 3: Utilização SOMASE

Imagine que você precisa resumir os valores de venda por categoria. No Excel é possível fazer esse cálculo utilizando o SOMARPRODUTO que soma os produtos de Valor Unitário * Quantidade:

=SOMARPRODUTO((Tabela1[CategoriaProduto]=[@Categoria])Tabela1[ValorUnitario]Tabela1[Quantidade])

Figura 4: Utilização SOMAPRODUTO

O total fazemos somando os valores de vendas de todas as categorias com SOMA:

=SOMA(Tabela4[ValorVenda])

Figura 5: Utilização SOMA

E com o total conseguimos calcular o percentual que categoria tem de representatividade dividindo o valor de cada uma separadamente pelo total:

=[@ValorVenda]/$C$19

Figura 6: Cálculo de percentual sobre o Total

Vimos aqui alguns cálculos e exemplos de utilização de fórmulas que são bem comuns no dia a dia de quem usa o Excel. Esses serão os mesmo cálculos que vou fazer no Power BI utilizando DAX. Porém, para importar nossa base precisamos separar nossas tabelas em abas.

Dica: Quando for trabalhar cm tabelas do Excel no Power BI tenha elas em abas separadas (pode ser no mesmo arquivo). Essa forma de organização fica mais estruturada quando você for obter os dados.

Obtendo dados

A nossa base em forma de abas ficou da seguinte maneira no Excel:

Figura 7: Tabela dividas uma em cada aba

Dessa maneira quando formos extrair os dados desse arquivo para o Power BI ele irá vir de forma correta. Então, em “Obter dados” vamos trazer somente essas duas abas para trabalhar:

Figura 8: Seleção do tipo de arquivo da base

Figura 9: Seleção do arquivo

Importante: É de extrema importância você selecionar “Transformar dados” após escolher o arquivo e abas que irá obter os dados para o Power BI para analisar se sua base veio de forma correta (estrutura, tipo de dados, etc…) antes de iniciar os cálculos do modelo.

Figura 10: Seleção das abas

Quando fazemos isso, vamos para um novo ambiente no Power BI que chama Power Query! O Power Query é uma ferramenta para você refinar seus dados e está disponível tanto para o Power BI quanto para o Excel. É uma ferramenta com diversas opções para tratar seus dados e hoje não vou detalhar o uso dela para não fugir do contexto. Porém, você vai encontrar em diversos artigos do blog a utilização do Power Query.

Dica: Leia o informativo (link abaixo) da Microsoft sobre o Power Query que explica mais detalhes da ferramenta que tenho certeza que pode te ajudar no dia a dia. Link: https://support.microsoft.com/pt-br/office/power-query-vis%C3%A3o-geral-e-aprendizagem-ed614c81-4b00-4291-bd3a-55d80767f81d

Figura 11: Ambiente do Power Query

Figura 12: Renomear nomes de tabelas

Os dados que importamos estão na forma colunar, ou seja, um tipo de informação por coluna. Além disso, estão sem erros e com o tipo de dados corretos. Então, não temos nenhuma etapa de transformação para aplicar e podemos “Fechar e aplicar” para sair do Power Query e voltar para o ambiente do Power BI.

Figura 13: Saindo do ambiente do Power Query

Abas de navegação

No Power Bi, temos 3 abas laterais de navegação que nos mostram o relatório de forma diferentes:

Figura 14: Abas de navegação lateral

Figura 15: Aba de “Relatório” com as opções de visualizações para criação

Figura 16: Aba de “Dados” com os dados das tabelas

Figura 17: Aba de “Modelo” com o relacionamento das tabelas

Cálculos

Temos duas formas de fazer cálculos no Power BI, que é ou por colunas ou por medidas. Normalmente, quem vem do Excel tem mais familiaridade e facilidade para fazer os cálculos em forma de coluna no inicio. Até porque as fórmulas feitas no Power BI utilizam a linguagem DAX que se assemelha a linguagem do Excel, porém são todas em inglês.

Vamos fazer um exemplo dessa forma para entendermos melhor com a função LOOKUPVALUE, que se assemelha muito a PROCV, para trazer os valores unitários dos produtos para a tabela fVendas:

Figura 18: Criando nova coluna na aba “Dados”

Figura 19: Argumentos

Fórmula DAX:

ValorUnit =
LOOKUPVALUE ( dProduto[ValorUnitario]; dProduto[idProduto]; fVendas[idProduto] )

Essa é uma das formas de trazer esse valor para a tabela fVendas. Outra forma seria utilizando o auxilio dos relacionamentos com a função RELATED:

ValorUnit =
RELATED ( dProduto[ValorUnitario] )

O resultado é o mesmo:

Figura 20: Resultado com RELATED

Agora, como temos tanto a coluna de Quantidade quanto de Valor Unitário podemos fazer a conta do valor da venda. O processo de cálculo no Power BI é de forma colunar, então quando montamos a fórmula em DAX não aparece a opção de célula:

Figura 21: Cálculo colunar

Valor Venda =
fVendas[Quantidade] * fVendas[ValorUnit]

Utilização de Relacionamentos

Lembra lá no Excel que para trazer a quantidade total vendida por produto com auxilio da função SOMASE? No Power BI, como temos o relacionamento criado entre as tabelas ao criar um visual com o nome do produto ele aplica um filtro (utilização do contexto para cada linha) e já trás o valor da quantidade para aquele determinado produto. Vamos ver um exemplo?

Figura 22: Aplicação de contexto com uma tabela

Você pode estar se perguntado, o que aconteceria se não tivesse o relacionamento entre as tabelas? Essa é uma ótima pergunta e vamos excluir o relacionamento para ver o resultado:

Figura 23: Excluindo o relacionamento

Figura 24: Resultado das quantidades na tabela sem relacionamento

Nesse caso, todos os valores aparecem iguais ao Total porque o Power BI não entende nenhum filtro aplicado na linha do NomeProduto. Imagino, que já entenderam a importância dos relacionamentos do modelo no Power BI. Com isso entendido, voltamos e criamos novamente o relacionamento entre as tabelas em “Modelo”.

Vamos fazer outro exemplo? Lembra que calculamos o total de venda por categoria usando o SOMARPRODUTO??! Iremos fazer um visual de tabela sem precisar usar fórmulas e pelo relacionamento entre as tabelas o valor vem correto:

Figura 25: Tabela com categoria e valor de venda

A primeira etapa em qualquer projeto é você entender e preparar seu modelo de forma correta!! Isso vai otimizar seu tempo quando for fazer seus cálculos e vai garantir uma resposta correta.

Cálculos: Colunas vs Medidas

Como mencionei anteriormente, temos duas formas de calcular os valores no Power BI. Então, é natural que você esteja se perguntando quando utilizar cada uma delas. Um bom resumo sobre quando utilizar e a definição de cada uma seria:

Formas de utilizar a Linguagem DAX:

1. Coluna calculada: quando precisamos colocar um valor no Eixo de um gráfico ou como filtro!
   • são calculadas linha a linha de uma tabela (contexto de linha)
   • desvantagens: consomem memória e são estáticas
2. Medidas: quando precisamos colocar cálculos como valores nos gráficos
   • são agregações (contexto de filtro)
   • vantagens: não consome memória e são dinâmicas

Em medidas, não conseguimos acessar diretamente uma coluna. O Power BI não entende qual linha da tabela você está avaliando. Veja a montagem de uma medida de Valor de Venda com o mesmo formato da coluna calculada:

Figura 26: Criação de nova medida

Figura 27: Argumentos não aceitos

Os argumentos não foram aceitos na função em DAX para a medida! Então, temos que fazer uma agregação para definir o contexto de avaliação para o cálculo. Vamos deixar esse exemplo um pouco de lado, e iremos fazer o cálculo de quantidade.

Acredito que você lembra a questão de contexto que comentei quando criamos nossa tabela com as quantidades, certo? Como temos o relacionamento não precisamos da avaliação SE do Excel! Então, a agregação necessária para calcular a quantidade é somente a soma, que em DAX é a fórmula SUM:

Medida:

Quantidade (Medida) =
SUM ( fVendas[Quantidade] )

O que acha da gente comparar o resultado do cálculo dessa medida com a que está na tabela que montamos anteriormente?

Figura 28: Valor da medida adicionado na tabela

Esse tipo de medida que criamos é chamada de medida explicita! Quando a medida é criada via coluna nomeamos de medida implícita. Nessa tabela a “conta” que foi feita com a medida implícita foi de soma, porém temos outras opções de operações que podem ser realizadas:

Figura 29: Opções de operações em medidas implícitas

Em medidas explicitas, não temos a opção de alterar a operação pois já determinamos isso na fórmula.

Voltando no exemplo da nossa medida explicita de valor de venda, precisamos da função SUMX que é semelhante a SOMARPRODUTO do Excel:

Valor Venda (Medida) =
SUMX ( fVendas; fVendas[Quantidade] * fVendas[ValorUnit] )

Essa função insere um contexto de linha no nosso cálculo. Então, para cada linha da tabela de fVendas o Power BI multiplica o valor de quantidade pelo valor unitário.

Colocando na nossa tabela:

Figura 30: Tabela com a medida criada

Veja que os resultados são iguais! Agora, ainda temos a oportunidade de melhorar nosso desenvolvimento. O valor unitário já está na nossa tabela dProduto e temos a possibilidade de trazer esse valor para a medida com a função RELATED.

Valor Venda (Medida) =
SUMX ( fVendas; fVendas[Quantidade] * RELATED ( dProduto[ValorUnitario] ) )

Tendo as medidas criadas, podemos apagar as colunas em “Dados” para otimizar nosso modelo:

Figura 31: Excluindo as colunas das tabelas

Cálculo de Percentuais

Normalmente, nessa parte que a maioria das pessoas começa a ter uma certa dificuldade. isso acontece, pois como havia falado trabalhos de forma colunar no Power BI e não fica tão direto apontar um valor de total para fazer um cálculo de percentual, por exemplo.

Aqui temos que aplicar um conceito para alterar o contexto do cálculo. A função que utilizamos em DAX para isso é a CALCULATE.

Para fixar melhor essa ideia de conceito, vamos pegar como exemplo a linha de “Legumes” na tabela de categoria x valor de venda:

Figura 32: Exemplo de contexto

O primeiro passo é criar uma medida para fazer o cálculo do total:

Medida:

Valor Venda Total =
CALCULATE ( [Valor Venda (Medida)]; ALL ( fVendas ) )

A função CALCULATE permite a alteração do contexto aplicando um filtro a uma expressão ou uma medida (no caso a medida de Valor Venda) e a função ALL retira qualquer filtro aplicado (nesse caso a tabela fVendas). Podemos ver o resultado dessa medida na tabela:

Figura 33: Resultado da medida Valor Venda Total

E agora para calcular o percentual é só dividir uma pela outra com a função DIVIDE:

Medida

% Vendas =
DIVIDE ( [Valor Venda (Medida)]; [Valor Venda Total] )

Figura 34: Resultado da medida de % de Vendas

Hierarquia

Imagine agora que temos uma matriz e nela a hierarquia na linhas com categoria de produto e o nome do produto:

Figura 35: Matriz com resultado da medida de % de Vendas

Figura 36: Matriz com hierarquia

Figura 37: Valor total geral, não só de uma categoria

Como vimos acima o valor venda total é do geral, mas não permite uma avaliação por categoria. Nos casos de hierarquia, temos que “devolver” o filtro para o cálculo com a função VALUES. Nesse caso em específico, iremos devolver o filtro de CategoriaProduto:

Medida

Valor Venda Categoria =
CALCULATE (
    [Valor Venda (Medida)];
    ALL ( fVendas );
    // Retira todos os filtros 
    VALUES ( dProduto[CategoriaProduto] ) // Devolve o filtro de CategoriaProduto
)

Figura 38: Matriz com a medida respeitando a hierarquia de categoria

ALL vs ALLSELECTED

Para o próximo exemplo, vou precisar do auxilio de uma tabela de apoio de calendário. Para criar essa tabela usamos a CALENDARAUTO:

Figura 39: Criação de nova tabela

Fórmula DAX

dCalendario =
CALENDARAUTO ()

Figura 40: Criação do relacionamento da nova tabela

E agora, criamos duas medidas que têm o contexto alterado. A primeira vai ser a Valor Venda ALL Calendario que vai tirar todo o contexto da tabela dCalendario (inclusive filtros fora do visual) do cálculo. E a outra será a Valor Venda ALLSELECTED Calendario que tira do contexto do visual onde está sendo usada a medida, mas respeita filtros extermos.

Não entendeu??! Fica tranquilo que no exemplo fica fácil…

Medidas

Valor Venda ALL Calendario =
CALCULATE ( [Valor Venda (Medida)]; ALL ( dCalendario ) )

Valor Venda ALLSELECTED Calendario =
CALCULATE ( [Valor Venda (Medida)]; ALLSELECTED ( dCalendario ) )

Figura 41: Exemplo com nenhum filtro externo aplicado

Figura 42: Exemplo com filtro externo aplicado

Importante: Fique atento aos filtros que irá disponibilizar e impactos que eles têm em seus visuais, pois é possível causar duvidas no usuário sobre os resultados apresentados.

Bom galera, com isso temos todos os cálculos que tínhamos feito no Excel aqui no Power BI. Além disso, pegamos outros exemplos de contexto de avaliação que com certeza estará na sua rotina com a utilização do Power BI!

Se tiver alguma dúvida ou sugestão de temas para as próximas lives deixe seu comentário aqui embaixo.

Até mais!

Abraços,
Leonardo.