“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?

www.youtube.com/rafaelmendonça

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