Calculando MÉDIAS com DAX

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).

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.

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

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:

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:

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:

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.

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

Se você reparar na figura acima também alteramos a curva para ficar no modo “nível”. Para isso, ativamos essa opção em Formato → Ní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:

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.

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]
)

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.