Agrupamento dinâmico para comparação de resultados

0. Contextualização

Você já precisou agrupar alguns valores de forma dinâmica para realizar comparações, seja entre meses, funcionários, campanhas ou produtos?

Hoje vou trazer uma forma simples de fazer esse tipo de análise.

1. Entendendo os dados

Temos 4 tabelas. 3 são tabelas dimensões (dCalendario, dCampanha e dFuncionario) e 1 tabela fato (fFacebook), que estão relacionadas conforme a imagem abaixo.

Esses dados trazem informações acerca do desempenho das campanhas no Facebook.

O nosso objetivo aqui é conseguir verificar o valor gasto nos meses selecionados em relação aos meses que não foram selecionados. Desse modo, podemos fazer diversas comparações e obter insights valiosos para o negócio.

2. Criação da tabela auxiliar

A primeira coisa que devemos fazer é criar uma tabela auxiliar contendo todos os meses e número dos meses para posterior ordenação. Podemos fazer isso tanto no Power Query, quanto via DAX ou até mesmo inserindo os valores manualmente.

Vou ensinar o jeito que considero mais performático:

No Power Query, devemos criar uma consulta em branco.

Com a consulta (query) criada, basta abrirmos o editor avançado e inserir o código abaixo:

let
    Source = Table.Distinct( // Traz apenas os valores distintos 
        Table.SelectColumns( // Seleciona as colunas desejadas
        dCalendario, // Informa a tabela de onde as colunas são
        {"Month Name", "Month"} // Lista de colunas desejadas
        )
    )
in 
    Source

Feito isso, obteremos uma lista de valores únicos dos nomes e números dos meses.

Porém, ainda falta um detalhe que é importantíssimo para a construção da nossa análise, as linhas com os valores de “Demais Meses” e “Meses Selecionados”.

2.1. Inserindo novas linhas no Power Query

Mas é possível inserir novas linhas diretamente no Power Query por meio da Linguagem M? Sim, é.

Para isso, é preciso utilizar a função Table.InsertRows, conforme vou detalhar:

Table.InsertRows( // Função que permite inserir novas linhas na tabela 
    Source, // Tabela na qual serão feitas as inserções
    Table.RowCount(Source), // Essa função retorna a quantidade de linhas da tabela e é utilizada para que a inserção seja         feita na última posição. Caso a ideia seja incluir as linhas acima daquelas já existente, basta utilizarmos o 0 (pois o Power Query começa a contagem do 0 e não do 1)
    { // Inicia a lista de valores a serem incluídos (as {} representam as listas) 
        [Month Name = "Demais Meses", Month = -1], // 1º registro a ser incluído (os [] representam registros)
        [Month Name = "Meses Selecionados", Month = 0] // 2º registro a ser incluído
    }
)

Com isso, é só juntarmos as informações e incluirmos essa função ao final do nosso código, no Editor Avançado, para obtermos o resultado que precisamos.

let
    Source = Table.Distinct(Table.SelectColumns(dCalendario, {"Month Name", "Month"})),
    NewRows = Table.InsertRows(Source, Table.RowCount(Source), {[MonthName = "Demais Meses", Month = -1], [Month Name = "Meses Selecionados", Month = 0]})
in
    NewRows

Agora basta fechar e aplicar as alterações feitas no Power Query, caso esse tenha sido o método utilizado para a criação da tabela auxiliar.

3. De volta ao Power BI

De volta ao Power BI, vamos verificar se nenhum relacionamento foi criado, pois iremos construí-lo de maneira virtual.

Com essa conferência, vamos em frente.

3.1. Obtendo o valor total gasto

Como queremos ver o montante gasto, é preciso criar nossa primeira medida para obter a soma dos valores.

Utilizaremos a função SUM, que faz a soma de determinada coluna.

Total Gasto = SUM(fFacebook[Valor gasto (BRL)])

3.2. Criando a medida mágica 😀

Como vimos anteriormente, não podemos ter qualquer relacionamento físico com a tabela auxiliar, pois o criaremos de maneira virtual.

Nesse caso, utilizaremos as funções INTERSECT e EXCEPT, que farão a

intersecção e a exceção dos valores selecionados (tabela dCalendario) com o eixo do gráfico que criaremos em breve (tabela dCalendario_Aux).

Total Gasto - Meses =
VAR vMesesSelecionados = // obter o total gasto dos meses que foram selecionados no filtro e, consequentemente, estão no contexto do visual
    CALCULATE(
        [Total Gasto],
        INTERSECT( // verifica quais valores foram filtrados na dCalendario por meio do filtro e faz a intersecção com os valores do eixo do gráfico
            VALUES(dCalendario[Month Name]), // valores selecionados na dCalendario 
            VALUES(dCalendario_Aux[Month Name]) // valores selecionados na dCalendario_Aux
        )
    )
   
   
VAR vMesesNaoSelecionados = // obter o total gasto dos meses que não foram selecionados
    CALCULATE(
        [Total Gasto],
        EXCEPT( // traz todos os valores presentes no filtro (dCalendario, coluna Month Name) que não foram selecionados
            ALL(dCalendario[Month Name]), // todos os valores existentes na coluna Month Name da dCalendario
            VALUES(dCalendario[Month Name]) // valores selecionados na dCalendario
        )
    )
    
    
VAR vTotalSelecionado = // obter o total gasto dos meses que foram selecionados no filtro
    CALCULATE(
        [Total Gasto], 
        FILTER(
            VALUES(dCalendario[Month Name]), // valores que foram selecionados no filtro             
            COUNTROWS(VALUES(dCalendario[Month Name])) < COUNTROWS(ALL(dCalendario[Month Name])) // essa condicional é feita para que a coluna de "Demais Meses" não apareça quando não houver nenhum valor selecionado no filtro (bem como quando todos os valores es tiverem selecionados). Portanto, se você deseja trazer a soma dos valores selecionados de maneira padrão, basta remover essa condição
        )
    ) 
    
    
RETURN 
SWITCH(
    SELECTEDVALUE(dCalendario_Aux[Month Name]), // obter qual é o valor selecionado no contexto do eixo do gráfico
    "Demais Meses", vMesesNaoSelecionados, // caso o valor seja "Demais Meses", retornará o valor da variável vMesesNaoSelecionados
    "Meses Selecionados", vTotalSelecionado, // caso o valor seja "Demais Meses", retornará o valor da variável vTotalSelecionado
    vMesesSelecionados // se não for nenhuma das duas, retornará o valor da variável vMesesSelecionados
)

3.3. Criando os visuais

Agora que já criamos a tabela auxiliar e as medidas necessárias, está na hora de ver o resultado em um gráfico, certo?

Primeiro incluiremos um filtro com os meses, para poder selecionar o período desejado. Utilizaremos, para tanto, a coluna Month Name da tabela dCalendario.

Em seguida, criaremos um gráfico de barras (ou o que for mais adequado para a sua solução) e utilizaremos, no eixo x, a coluna com o nome do mês da tabela auxiliar que criamos anteriormente. Por sua vez, no eixo y, colocaremos a nossa medida Total Gasto - Meses.

Por fim, é só fazermos os filtros necessários e pronto. Comparação pronta para facilitar nossas análises.

O mais legal desse visual é que ele permite analisar os dados de qualquer perspectiva, bastando criar a tabela auxiliar respectiva e fazer as alterações nas colunas necessárias.

Espero que tenham gostado e até a próxima!