Blog Power BI Experience

Calculando MÉDIAS com DAX

Leonardo Karpinski
Leonardo Karpinski

Founder da Power BI Experience

Facebook
Twitter
LinkedIn

E aí, tudo bem?! O conteúdo de hoje será sobre DAX.

Nosso objetivo será calcular:

  1. Ticket Médio
  2. Preço unitário médio
  3. Faturamento médio diário
  4. Faturamento médio diário no mês

Base de Dados

Nossa tabela fato consiste possui as notas fiscais e produtos vendidos para cada nota (quantidade, preço e valor da venda).

Figura1: Tabela fato com as vendas

Como você faria para calcular o ticket médio de todas as vendas, ou seja, o valor médio de cada venda?

Se você pensou em fazer a média da coluna Valor Venda, você provavelmente não se atentou ao fato de que há mais de uma linha para cada Nfe. E como cada venda é representada por uma Nfe diferente, não podemos fazer dessa forma.

Ticket Médio

Primeiro vamos criar um cartão com a média do valor de vendas arrastando a coluna Valor Venda no campo desse visual apenas para comparar com o que faremos via DAX.

Figura 2: Média da coluna Valor Venda

Para calcular a média do Valor venda precisamos do total desse valor (soma) e da quntidade, certo?!

A soma da venda será dada por:

Faturamento =
SUM ( fVendas[Valor Venda] )

A quantidade será:

Qtd Vendas =
DISTINCTCOUNT ( fVendas[Nfe] )

Ao usar o DISTINCTCOUNT você está obtendo a soma da quantidade de vendas considerando apenas Nfe’s distintas.

Então o faturamento por Nfe (Ticket Médio) será a divisão desses dois valores:

Ticket Médio =
DIVIDE (
    [Faturamento],
    [Qtd Vendas]
)

Podemos calcular esse Ticket Médio numa medida só usano AVERAGEX e VALUES, veja:

Faturamento Diário AVERAGEX =
AVERAGEX (
    VALUES ( dCalendario[Date] ),
    [Faturamento]
)

Note que o resultado que tivemos arrastando a coluna Valor Venda para o cartão é diferente do que calculamos via DAX (Ticket Médio) porque no primeiro apenas fizemos o valor médio da coluna Valor Venda sem levar em conta as Nfe’s. Beleza?!

Figura 3: Média do Valor Venda versus Ticket médio

Durante a Live#32, um participante perguntou:

E se eu precisar agrupar por mais de uma coluna?

Nesse caso, você poderia utilizar o SUMMARIZE. Veja um exemplo:

Ticket Médio AVERAGEX e SUMMARIZE =
AVERAGEX (
    SUMMARIZE (
        fvendas,
        fVendas[Nfe],
        fVendas[Produto]
    ),
    [Faturamento]
)


Preço Unitário Médio

Usando a própria coluna com Preço Unitário, a média fica assim:

Figura 4: Média da coluna Preço Unitário

Para levar em conta a Nfe, precisamos calcular da seguinte forma:

Preço Un. Médio Venda =
DIVIDE (
    [Faturamento],
    SUM ( fVendas[Quantidade] )
)

Compare os dois valores para essa Nfe:

Figura 5: NF03

Faturamento Médio Diário

Há algumas formas e calcular o faturamento médio diário.

Com AVERAGEX:

Faturamento Diário AVERAGEX =
AVERAGEX (
    VALUES ( dCalendario[Date] ),
    [Faturamento]
)

Com DIVIDE e DISTINCTCOUNT:

Faturamento Diário DIVIDE =
DIVIDE (
    [Faturamento],
    DISTINCTCOUNT ( fVendas[Data] )
)

Repare que aqui eu estou utilizando a coluna [Date] da tabela fVendas porque quero calcular o faturamento médio somente considerando dias que tiveram venda, beleza?!

Você deve estar se perguntando porque eu utilizei a coluna [Date] da tabela dCalendario e não da tabela fVendas.

É que nos dias que eu não tive faturamento, o AVERAGEX vai retornar um valor em branco. Entendeu?!

E se você precisar mostrar essa média num gráfico de barras no qual o Eixo X estão os dias , como você deveria fazer?

Se utilizarmos qualquer uma dessas duas medidas que acabamos de criar (Faturamento Diário), o gráfico ficará assim:

Figura 6: Plotando o faturamento diário médio

Porque Repare que precisaremos utilizar o conceito de contexto para ajustar nossa medida:

Faturamento Diário Geral =
VAR vMedia =
    CALCULATE (
        [Faturamento Diário DIVIDE],
        ALL ( dCalendario )
    )
RETURN
    IF (
        [Faturamento]
            <> BLANK (),
        vMedia
    )


Note que usamos a função ALL na CALCULATE porque precisamos garantir que essa média fique constante ao longo do Eixo X, certo?!

Relembrando:
ALL: Retorna todas as linhas de uma tabela ou todos os valores de uma coluna, ignorando todos os filtros que estiverem aplicados. Essa função é útil para limpar filtros e criar cálculos em todas as linhas em uma tabela.

Figura 7: Exibição da medida Faturamento Diário Geral


Faturamento diário no mês

Para calcularmos o faturamento diário de forma que a média mude a cada mês, devemos adicionar somente um trecho novo na fórmula em relação à anterior, veja:

Faturamento Diário no Mês =
VAR vMedia =
    CALCULATE (
        [Faturamento Diário DIVIDE],
        ALL ( dCalendario ),
        VALUES ( dCalendario[Date].[Month] )
    )
RETURN
    IF (
        [Faturamento]
            <> BLANK (),
        vMedia
    )


Note que inserimos mais um argumento na CALCULATE: a função VALUES. Como não temos uma coluna de mês na base, utilizamos a hierarquia nativa da nossa dCalendario (é esse .[Month]).

Bora ver como ficou?!

Figura 8: Faturamento diário no mês

Se você reparar na figura acima também alteramos a curva para ficar no modo “nível”. Para isso, ativamos essa opção em FormatoNível na seção Formas.

Bom, até aqui matamos tudo que queríamos né?!

Mas, ainda não acabou…

Durante a live me pediram para calcular a média considerando os dias úteis.


Faturamento médio diário – dias úteis

Se você der uma olhada com calma na nossa tabela e nos cálculos que fizemos vai notar que nós dividimos o faturamento diário pelo número de dias, independente se esse dia era ‘útil’ ou não.

Para matar esse problema iremos primeiro adicionar uma coluna na dCalendario com a classificação do dia em Útil e não útil.

Para fazer isso, clique na tabela dCalendario com o botão direito em “Nova Coluna”. Depois insira a fórmula:

Dia Util =
VAR vWeekday =
    WEEKDAY ( dCalendario[Date] )
RETURN
    IF (
        vWeekday = 1
            || vWeekday = 7,
        “Não Útil”,
        “Útil”
    )

Para simplificar nós consideramos como “Dia útil” aquele compreendido entre Segunda e Sexta-feira, ok?! Você já deve ter percebido, mas não custa ressaltar: a função WEEKDAY considera Domingo como “1” e Sábado como “7”).

Próximo passo será ajustar nossa medida para que a gente considere no ‘denominador’ apenas esses dias úteis através de um filtro na CALCULATE, veja:

Faturamento Diário – Dias Úteis =
VAR vDiasUteis =
    CALCULATE (
        COUNTROWS ( dCalendario ),
        dCalendario[Dia Util] = “Útil”
    )
VAR vMedia =
    DIVIDE (
        [Faturamento],
        vDiasUteis
    )
RETURN
    vMedia

Resultado:

Figura 9: Faturamento médio diário (dias úteis)

Olha que legal! A média ficou bem maior porque agora nosso ‘denominador’ da fórmula da média ficou menor. Faz sentido, né?!

Se você acha que acabou, calma aí!

Um participante durante a live também me pediu para fazer o cálculo da média móvel!


Média Móvel

Para esse exemplo, eu peguei uma base de dados um pouco maior para vocês conseguirem ver melhor o resultado da medida, beleza?!

Quando você olha esse gráfico de faturamento por dia, percebe que fica difícil identificar as tendências nesse período específico.

O pessoal do mercado financeiro costuma suavizar esses altos e baixos para através de média móvel.

Figura 10: Faturamento com grande variação entre os dias

Vamos supor que você precise calcular a média dos últimos 180 dias para cada dia do Eixo X. Veja que não tem mistério:

Média Móvel 180 Dias =
AVERAGEX (
    DATESINPERIOD (
        dCalendario[Data],
        MAX ( dCalendario[Data] ),
        -180,
        DAY
    ),
    [Total Vendas]
)

Figura 11: Resultado da média móvel 180 dias

Agora sim, fechou!

O conteúdo de hoje foi mais curtinho mas espero que tenha te ajudado porque em algum momento na sua vida você precisará calcular uma média em DAX.

Abraços,
Leonardo.