Pular para o conteúdo principal

Power BI - NÃO EDITE seus Modelos do Power BI na Nuvem.

“Traduzindo” formulas do Excel para DAX.



Como utilizar algumas formulas bem conhecidas no EXCEL em DAX



Olá moçada tudo bem?

Todos sabemos que muitos dos usuários de Excel tem dificuldade de migrar ou utilizar o Power BI, muito pela dificuldade de entender a forma de calcular do DAX e os contextos de linha e de filtro, bem como por já estar acostumado com as formulas no Excel, chegando a procurar um SOMASE (SUMIF) quando quer escrever uma nova medida em DAX (eu fiz isso).

Queria então conversar hoje com esse público e te mostrar como traduzir o que você pensa em EXCEL para DAX.

Iremos brincar então com as seguintes formulas em Excel (versão português).

Somase, Somases, Procv, Cont.se, Cont.ses, Pgto, Concat, Texto, Esquerda, Direita e Seerro.

Utilizaremos para nosso exemplo o modelo de dados abaixo (Power BI e Excel)





Nossos objetivos serão:

- Encontrar o total de Compras para veículos novos (ano de venda igual a 2018).
- Encontrar o total de Compras para veículos novos e com valor de venda igual a R$ 1Mi.
- Encontrar qual o veículo vendido para o cliente Rafael.
- Identificar quantos veículos foram vendidos acima de R$ 100 Mil.
- Identificar quantos veículos foram vendidos com valor superior a R$ 100 Mil e com Venda A Prazo.
- Criar uma coluna Key para as vendas onde essa Key será a concatenação das 3 primeiras letras do nome do cliente com as 3 primeiras letras do veículo adquirido com a data no formato YYYYMMDD.
- Encontrar o valor da parcela dos clientes com base no periodo e juros informado.
Utilizaremos apenas expressões no Excel de forma simples, sem o uso de Funções Matriciais como um usuário menos avançado do Excel faria, bem como não faremos uso do PowerQuery editor para facilitar algumas das operações no Power BI.

Mas ANTES... não esquece de dar uma passada la no meu canal no youtube, tem sempre novidade por lá também, inscreva-se e ative o sininho pra receber as notificações... não vai demorar nem 10 segundos e você já volta pra cá talquei?


1)     Veículos Novos.
No Excel para identificarmos o total de veículos novos comprados pelos nossos clientes iremos utilizar a coluna “Veículo” para extrair os últimos 4 caracteres que representam o ano do veículo e somente após isso utilizaremos um Somase para identificar aqueles veículos novos.

Ano Veículo:

=DIREITA(B2;4)


Valor Veículos Novos Vendidos:

=SOMASE(G2:G5;2018;C2:C5)




No Power BI não teremos nenhuma necessidade de utilizar uma coluna auxiliar para efetuar esse cálculo, bastando apenas utilizar uma formula que soma apenas os valores filtrados de uma coluna, a SUMX.

Em DAX as expressões terminadas com X tem uma particularidade interessante, que é a possibilidade de filtrar tabelas e depois executar o cálculo linha a linha, de acordo com o filtro aplicado na expressão, chamamos isso de Contexto de LINHA.

Teremos portanto a seguinte SUMX

Veículos Novos =
SUMX (
    FILTER ( Tabela1; RIGHT ( Tabela1[ Veículo ]; 4 ) = "2018" );
    Tabela1[Valor de Compra]
)


Abaixo eu marquei em cores diferentes a expressão, equivalente aos dois argumentos obrigatórios, que são em Vermelho uma Tabela e em Azul o valor que eu desejo somar a cada linha analisada da tabela.


Notem que no primeiro argumento eu não trouxe simplesmente a tabela no seu formato original “Tabela1”, mas sim a tabela de forma Filtrada aplicando a expressão FILTER. Como argumento da FILTER inserimos uma expressão chamada RIGHT, que faz a mesma função da formula DIREITA no Excel, pega um valor e me retorna quantos caracteres eu quiser a partir da direita. Utilizamos esse RIGHT para identificar na minha tabela os valores filtrados referente a veículos do ano de 2018.

O resultado retornado será o mesmo da formula em Excel.


2)     Veículos novos com valor superior a R$ 1 Mi.

Utilizando agora o SOMASES no Excel iremos calcular o total de valor dos valores com base em 2 critérios, ano 2018 e valor maior que R$ 1.000.000,00:

=SOMASES(C2:C5;G2:G5;2018;C2:C5;">1000000")



Já no DAX iremos utilizar uma formula praticamente igual à do item anterior adicionando apenas mais uma condição ao Filtro da tabela:

Veículos Novos > 1 Mi =
SUMX (
    FILTER (
        Tabela1;
        RIGHT ( Tabela1[ Veículo ]; 4 ) = "2018"
            && Tabela1[Valor de Compra] > 1000000
    );
    Tabela1[Valor de Compra]
)



Para adicionar essa segunda condição utilizei os conectores && que tem dentro do DAX a função de “AND” (“E”). Caso quisemos no lugar de “E” encontrar uma condição onde uma “OU” outra situação fosse verdadeira teríamos utilizado o conector || (2 pipes).

3)     Veículo vendido ao cliente “Rafael”.

Vocês já sabem que quando precisam identificar um valor que esteja em uma coluna com base o valor de outra coluna na mesma linha basta chamamos a bendita PROCV.

=PROCV("Rafael";A2:G5;3;FALSO)



Poderíamos aqui utilizar novamente a expressão SUMX no DAX, mas para variar e mostrar que em DAX podemos calcular algo de várias formas diferentes vamos utilizar a função CALCULATE.

Venda Rafael =
CALCULATE (
    SUM ( Tabela1[Valor de Compra] );
    FILTER ( Tabela1; Tabela1[ Cliente ] = "Rafael" )
)



A função CALCULATE é extremamente poderosa e assim como SUMX trabalha em contexto de LINHA. Notem que os argumentos são bem parecidos a uma SUMX. Na SUMX o primeiro Argumento era uma Expressão e o Segundo uma Tabela, na CALCULATE temos o contrário, primeiro a EXPRESSÃO e depois a TABELA.

E lá vem a pergunta...

“... mas Rafael porque na SUMX não informamos a expressão SUM e na CALCULATE sim?”

Bem, isso é a particularidade da SUMX, por ser na sua natureza uma expressão de SOMA o argumento utilizado pode ser o nome da própria coluna e ela entenderá que você irá somar o valor de cada linha analisada da coluna.

4)     Quantidade Veículos vendidos acima de R$ 100 Mil.

Em Excel temos uma expressão muito simples para essa situação a CONT.SE, vamos a ela

=CONT.SE(C2:C5;">100000")




Já em DAX vamos usar mais uma Expressão terminada em X, ou seja, que tem contexto de LINHA.

Acima de 100 Mil =
COUNTX (
    FILTER ( Tabela1; Tabela1[Valor de Compra] > 100000 );
    Tabela1[Valor de Compra]
)


O uso dela como vemos é similar a SUMX, sendo necessário uma TABELA e uma EXPRESSÃO) e assim como na SUMX utilizamos a tabela de forma FILTRADA com as condições que queríamos. VALOR DA COMPRA acima de 100.000.



5)     Veículos Acima de 100 Mil e a Prazo.

A expressão CONT.SE, assim como a SOMASE, tem a sua versão de múltiplas condições e com essa menina que brincaremos. Para o caso so precisamos então saber que nas vendas à vista a quantidade de parcelas foi 0 e essa será nossa segunda condição:

=CONT.SES(C2:C5;">100000";E2:E5;"<>0")




Como os Srs a essa altura já estão Phd em DAX, já presumiram que utilizaremos a mesma expressão no item anterior apenas adicionando no Filtro da tabela mais uma condição com os sinais && não é verdade?

Acima de 100 Mil a prazo =
COUNTX (
    FILTER (
        Tabela1;
        Tabela1[Valor de Compra] > 100000
            && Tabela1[Qtd Parcela] <> 0
    );
    Tabela1[Valor de Compra]
)




6)     Criar uma coluna Key para as vendas:

Queremos então agora criar uma coluna Key (Chave) que será a concatenação de 3 informações.
Vamos quebrar inicialmente em 3 funções no EXCEL para identificar todos os dados que farão parte dessa KEY.

3 primeiras letras do nome do cliente

=ESQUERDA(A2;3)


3 primeiras letras do veículo

=ESQUERDA(B2;3)


Data no formato YYYYMMDD (Ano com 4 dígitos, Mês com 2 dígitos e dia com 2 dígitos)

=TEXTO(D2;"AAAAMMDD")


Como já descobrimos cada elemento que comporá nossa key, vamos então uni-los com a expressão CONCAT.

=CONCAT(ESQUERDA(A2;3);ESQUERDA(B2;3);TEXTO(D2;"AAAAMMDD"))




Voltando então para o DAX, utilizaremos expressões praticamente IGUAIS, tanto nos nomes quanto nos argumentos, com exceção da CONCAT que se torna desnecessária no DAX. Dessa vez não criaremos uma medida, mas assim como no Excel, utilizaremos uma coluna calculada.

Key =
LEFT ( Tabela1[ Cliente ]; 3 ) & LEFT ( Tabela1[ Veículo ]; 3 )
    & FORMAT ( Tabela1[Data de Compra]; "YYYYMMDD" )


Viram como concatenamos os valores? Apenas utilizando a expressão & . Lembram lá atrás que utilizamos && para avaliar se uma expressão E outra são verdadeiras para um filtro? Prestem sempre atenção para não confundir & e && são totalmente diferentes.



7)     Encontrar o Valor de Cada Parcela

Temos então aqui uma vantagem na formula de cálculo do EXCEL. O Excel possui uma expressão para esta situação, mas o DAX não possui nada próximo e iremos então calcular na “mão”.
No Excel a formula PGTO nos traz de formula bem simples o cálculo:

  =SEERRO(PGTO(F2;E2;C2);0)




Se você não percebeu, utilizamos também a função SEERRO, que retorna zero quando houver erro na expressão utilizada, que ocorreria no caso da venda da Ferrari ao Garcia que decidiu pagar à vista.
Já no DAX. “senta que lá vem história...”

Teremos que fazer um cálculo totalmente manual para esse caso.

A expressão DAX seguirá o cálculo matemático abaixo:




PMT = Pagamento

i = Taxa de Juros

PV = Valor Presente

n = Período


Temos então, a seguinte fórmula:

Pagamento =
IFERROR (
    Tabela1[Valor de Compra] * Tabela1[Taxa de Juros]
        / (
            1
                - POWER ( 1 + Tabela1[Taxa de Juros]; - Tabela1[Qtd Parcela] )
        );
    0
)


E Aí Deu medinho de matemática? Não sabe brincar não desce pro play...

Vamos torcer para a Microsoft nos ajudar e trazer alguma formula mais simples para esse calculo.

E por hoje é isso pessoal espero que tenham gostado. Encontrou algum erro, tem uma dica? Manda aí nos comentários meu filho...

Um grande abraço


Comentários

  1. Muito top essas dicas!!! Vim pelo LinkedIn!

    ResponderExcluir
  2. Muito boa dica. Porém vi que conseguimos fazer uma “SOMASE” buscando um dado fixo. Exemplo: “>10000”.
    Como posso fazer uma soma com buscando na informação da linha de uma das colunas? Exemplo: Somase(A2:A200;B2;C2:C200), no caso a linha “B2”. Da mesma forma que fazemos no excel.

    ResponderExcluir

Postar um comentário

Postagens mais visitadas deste blog

[Power BI] O totalizador da minha Tabela ou Matriz "NÃO BATE" e agora?

Olá amantes de visuais de tabela e matriz no Power BI, tudo bem? Hoje vamos falar de um caso que vejo bastante nos grupos do Power BI. “O calculo na linha está batendo mas no total não e agora?” Mas antes de iniciarmos, que tal deixar aquela sua inscrição marota no meu canal no youtube que está recheado de vídeos legais, vai lá seu pão duro de like youtube.com/rafaelmendonca Agora que você já aliviou sua pão-durice você merece conteúdo .... kkkkkkkk Vamos lá, temos então a seguinte tabela gerada no meu conjunto de dados Essa tabela possui uma lista de vendas realizadas por dois vendedores com o numero da ordem da venda, um valor unitário e uma quantidade de itens em cada venda. O que queremos é bem simples meu jovem, calcular o valor total de cada venda e ter um total de todas as vendas... Aí você muito garotão usa a seguinte expressão DAX: E depois joga isso numa tabela. Lindo neh??? Mas o totalizador trouxe um valor que não tem

[Power BI] Você está com erro nas suas funções DAX que até ontem funcionavam?

Hoje você acordou abriu seu Power BI tentou criar uma nova medida e está dando um erro chato como esse abaixo A sintaxe de ";" está incorreta ou até mesmo o Muitos argumentos foram passados para a função Bem não precisa se preocupar porque você não desaprendeu Power BI, a sintaxe deve estar correta, mas correta para o Padrão que usamos aqui no Brasil com o ; (ponto e virgula) como separador de lista e a ,  (vírgula) como separador de decimal. Na versão de Maio de 2020 o Power BI Desktop jogou como padrão para todos não mais o padrão regional da sua máquina mas o padrão internacional do DAX usando , (vírgula) como separador de lista e . (ponto) como separador de decimal. Sendo assim se você quiser continuar usando o padrão da máquina tem que alterar a opção indo em Arquivo Opções e Configurações e depois Opções Na caixinha que é aberta vá em Configurações Regionais na área Global E altere a opção que está  Recomendado  para  Usar separador do DAX Localizado Vai ser solicita

5 coisas no Power Query que talvez nunca tenham te explicado ou mostrado.

5 coisas no Power Query que talvez nunca tenham te explicado ou mostrado. O Power Query   é o editor de consultas utilizado tanto no PowerBI   quanto no Excel (em algumas versões) e que tem enorme poder de transformação dos dados importados de fontes diversas, sejam elas estruturadas ou não. Usuários menos experientes utilizam o  Power Query  apenas com cliques nos elementos do MENU, o que sim pode ajudar bastante, mas entender as funções e detalhes que estão por trás desses cliques podem enriquecer o seu  ETL , resolver problemas que nunca seriam feitos com o simples uso dos botões e te ajudarão numa certificação Microsoft. 1 - Sabendo o que está acontecendo: Uma das coisas principais e essenciais no uso do  Power Query  é que, mesmo utilizando os botões no Menu, você consegue identificar que funções estão sendo utilizadas. Vou mostrar isso de duas formas: Barra de Formulas - Dessa forma você pode acompanhar na barra de formulas