E aí. tudo bem com você? Hoje vamos falar principalmente sobre modelagem de dados.

Vamos abordar assuntos do nível básico ao avançado, passando por tópicos como modelagem de dados, contextos de filtro e de linha, relacionamentos, cruzamento entre diferentes tabelas, comparativo de vendas com metas, diluição de meta, enfim… Um artigo completo para você começar a praticar e automatizar os seus processos, eliminando tarefas repetitivas.

Vou mostrar como você que já trabalha com excel pode desenvolver as mesmas tarefas utilizando Power BI.

Cenário

Antes de começar, quero dizer para você o seguinte, não se prenda ao tema, o importante aqui hoje é você entender os conceitos para que você possa aplicar na sua área, ok?

Agora vamos lá…

Nosso cenário é o seguinte, temos aqui a tabela de vendas.

A empresa que estamos analisando é uma distribuidora de frutas, legumes e verduras.

E de onde vieram esses dados? Vieram de uma extração do sistema.

Agora, suponha que o precisamos fazer uma análise vendas e custos, e comparativo com metas.

Como podemos fazer isso?

Primeiro extraímos as informações do sistema pois não temos acesso ao banco de dados ERP. Então exportamos os seguintes relatórios/tabelas:

Tabela de Vendas; Cadastro de Cliente; Custo; e Cadastro de Produto.

Esse é um cenário bem comum né?!

O analista extrai os dados e depois importa no Excel, onde faz cálculos complementares para elaborar suas análises, por exemplo para calcular o valor total de vendas, você iria criar uma coluna e multiplicaria a quantidade pelo valor unitário.

E qual é o problema disso?

O problema disso é a redundância de processos, no próximo mês, ou próxima atualização você terá que repetir todo o processo, extrair do sistema, refazer os cálculos no Excel, e só então poderá analisar os novos dados, ou seja, é um processo bem repetitivo né.

E se eu quiser analisar o valor total por tipo de cliente?

O tipo do cliente está em outra tabela, e nesse caso teríamos que fazer 02 “procv”: um para encontrar o idTipoCliente para cada venda e outro para encontrar a descrição do tipo de cliente.

Assim, podemos fazer uma tabela dinâmica para analisar melhor as vendas pelo tipo de cliente.

Lembrando que, o resultado da tabela dinâmica também poderia ser obtido através da função SOMASES.

Porém a tabela dinâmica é muito melhor né, pois é só arrastar as colunas, e sabe de uma coisa, o Power BI trabalha de forma muito semelhante à tabela dinâmica, a diferença é que no Power BI eu posso trabalhar com várias tabelas.

Agora vamos para o Power BI…

Mas antes… dica de ouro:

“Ao exportar um relatório de um sistema, nunca edite o arquivo! Faça todos os tratamentos e cálculos no Power BI.”

E é por isso que eu vou deletar as colunas que calculei de forma manual no Excel.

Agora sim, podemos ir para o Power BI.

Importando os Dados

Abra o Power BI desktop e siga os passos abaixo

Na janela aberta:

A janela aberta vai te mostrar todas planilhas e tabelas que constam no seu arquivo. Selecione conforme mostrado abaixo.

Ao clicar em transformar dados, abrirá a janela do Power Query.

Dica2: o primeiro passo deve ser sempre o de transformar os dados.

Dê uma boa olhada nos seus dados, para ver se não precisa de nenhum tratamento adicional. Nessa tabela de Vendas não tem nenhuma transformação a ser feita, veremos isso daqui a pouco.

Clique em Fechar e aplicar.

Aqui está a tabela de vendas que acabamos de carregar.

Vamos criar essa tabela dinâmica no Power BI:

No Power BI, selecione o visual destacado e arraste os campos da tabela vendas para os campos de configuração do visual, conforme demonstrado abaixo.

Para formatar a tabela criada, clique no rolinho, e defina o tamanho do texto.

Em seguida, ainda nesse campo de configurações, em Total geral, coloque preto.

Então teremos o mesmo resultado da dinâmica, sem nenhum cálculo.

Mas o que aconteceu aqui…

Nesse caso a CategoriaProduto está filtrando a quantidade de vendas, isso é que chamamos de contexto de filtro.

Vou explicar melhor… Veja por exemplo a categoria Frutas

É como se fossemos na tabela vendas e filtrasse a categoria Frutas.

A tabela ficaria assim…

Cada uma das linhas de categoria de produto, me gera uma tabela diferente em relação à tabela original.

Agora, vamos criar uma tabela com todos os dados das vendas.

Clique na seta exibida no cantinho de cada campo e selecione a opção “Não Resumir”.

Clicando em Frutas, a tabela debaixo é filtrada mostrando apenas as vendas das categoria Frutas, cuja quantidade total  é igual a 287.

Agora vamos analisar, o Total. Sempre somos levados a pensar que ele é a soma dos valores acima dele, porém isso nem sempre ocorrerá aqui no Power BI, mas calma… Não quer dizer que isso seja um erro, é apenas uma questão de contexto.

No Power BI, o total é um novo cálculo que contempla a tabela inteira de vendas sem nenhum filtro aplicado, e soma a quantidade.

Mais um exercício para você entender o contexto…

1 – Primeiro arraste o id cliente para o campo de colunas

2 – Vamos avaliar quais são os filtros aplicados na tabela original para computar o valor 68 destacado na tabela.

Categoria = Frutas

idCliente = Cl4

Sempre que for avaliar um resultado/contexto, procure entender quais são os filtros aplicados.

Importando Tabela dCliente

Vamos relembrar nossa tabela dinâmica…

Para visualizar em tabela dinâmica, o total de vendas por tipo de cliente, primeiro tivemos que fazer 02 procx para trazer os dados do cliente para a tabela de vendas, e depois criamos a dinâmica.

Mas e no Power BI, como isso seria feito?

Vá em Obter Dados -> Excel -> Selecione as tabelas “Cliente” e “TipoCliente”.

Não vamos aplicar nenhuma transformação nesse caso, então você pode fechar e aplicar.

Agora, em uma página nova:

1 – selecione visual de matriz

2 – coloque o “TipoCliente” no campo de Linhas

3 – coloque a “Quantidade” no campo valores.

Veja, na tabela criada que a quantidade filtrou de forma automática de acordo com o tipo de cliente, como isso acontece?? Relacionamentos entre tabelas

Tipo de Cliente -> filtra tabela Cliente-> filtra tabela de vendas. O relacionamento faz com que o filtro de uma tabela passe para outra.

Veja o que acontece quando excluímos o relacionamento. Clique com o botão direito na seta que liga Tabela Cliente e TipoCliente e selecione a opção “Excluir”.

Faça o mesmo com a seta que liga a tabela Cliente e a tabela Vendas.

Perceba que o valores agora são iguais em todas as linhas, isso acontece porque ao excluirmos o relacionamento, a dimensão cliente não consegue mais filtrar as vendas

Recrie os relacionamentos, para isso basta clicar e arrastar os campos correspondentes.

Vamos entender o que acontece por trás…

Foque no tipo de cliente “Restaurante”:

1. Na aba Dados, na tabela TipoCliente
2. Filtre o TipoCliente = Restaurante.

Na tabela Cliente, filtre o idTipoCliente = TP2

Na tabela de Vendas, filtre os idCliente = 4,5,6.

O resultado desses filtros é uma tabela contento os clientes 4,5,6 que fazem parte do tipo “Restaurante”, cujo total de quantidade é 348.

Agora, vamos importar a tabela de Metas

Veja a estrutura dela aqui no Excel, temos o valor das metas por categoria e mês.

Então, vamos importar essa tabela, para fazermos uma comparação de vendas versus meta.

Vá em Obter Dados:

1. Selecione a tabela de Metas
2. Clique em Carregar.

Importante: Nesse caso aqui, estou clicando direto em carregar dados, mas a boa prática manda que em projetos reais sempre escolha a opção “Transformar Dados”.

Então, temos aqui nossa tabelinha com quantidade por tipo de cliente, porém agora eu quero mostrar o valor total vendido, e o total da meta.

Como podemos fazer isso aqui no Power BI? Vamos utilizar a linguagem DAX.

Linguagem DAX

Podemos utilizar a linguagem DAX para criar colunas calculadas, medidas e tabelas, porém o seu uso principal é para criação de medidas.

O que podemos criar com a Linguagem DAX:

1) Coluna calculada: quando precisamos colocar um valor no Eixo de um gráfico ou como filtro

 – São calculadas linha a linha de uma tabela (contexto de linha)

– Desvantagens: 1) consomem memória; 2) são estáticas

2) Medidas: quando precisamos colocar cálculos como valores nos gráficos

– São agregações (contexto de filtro)

– Vantagem: não consome memória e são dinâmicas

3) Tabelas: quase nunca usamos com DAX (exceção para tabela dCalendario)

Primeiro cálculo, vamos calcular o total da quantidade, mas você deve estar se perguntando, porque calcular se eu já tenho esse valor.

Esse valor que utilizamos até agora, é uma medida implícita, e o ideal é trabalharmos com medidas explícitas, e eu já irei explicar a razão para isso.

Clique em cima da tabela de vendas, botão da direita, nova medida.

Digite o cálculo abaixo:

Soma de Quantidade = SUM ( Vendas[Quantidade])

Arraste esse novo cálculo para o campo de valores.

Note que a medida que acabamos de criar contém uma calculadora ao lado, isso é um indicativo de uma medida explícita.

Note abaixo que os valores são iguais, porém a principal diferença entre as duas é que a medida explícita pode ser reutilizada deixando a manutenção do seu projeto melhor.

Por exemplo, suponhamos que preciso aplicar o fator de 1,2 à quantidade, eu posso simplesmente utilizar a medida já criada e multiplicar por 1,2. Assim:

Quantidade Reajustada = [Soma de Quantidade]*1.2

Eu não preciso criar a soma da quantidade toda as vezes que precisar do valor da quantidade.

Outra vantagem da medida explícita, é a possibilidade de renomear de acordo com o que achar mais adequado, por exemplo, clique na barra de fórmulas e substitua o nome da medida de Soma de Quantidade para Quantidade Total.

Quantidade total = SUM(Vendas[Quantidade])

Mantenha no visual apenas a medida explícita.

Agora vamos calcular o valor total de vendas.

Primeiro faremos o valor total via coluna calculada:

1. Clique na aba dados
2. Selecione a tabela de vendas
3. Escolha a opção coluna calculada
4. Execute o cálculo:

ValorTotal = Vendas[Quantidade] * Vendas[ValorUnitário]

Acabamos fazer um cálculo utilizando o conceito de contexto de linha, pois o valor é calculado linha por linha da tabela.

Porém, o uso de coluna calculada tem algumas desvantagens, pois ela é estática e consome muita memória.

Agora vamos fazer o mesmo cálculo (valor total de vendas), dessa vez através de medida. Nesse caso, iremos utilizar uma função muita parecida com o SOMARPRODUTO do Excel.

Clique com o botão direito em cima da tabela de vendas -> nova medida.

Digite o cálculo abaixo:

Valor Total = SUMX ( Vendas, Vendas[Quantidade] * Vendas[ValorUnitario] )

Vamos avaliar quais são os contextos aplicados nesse cálculo, foque o Restaurante Catarina.

Nesse caso que estamos analisando temos 02 contextos, de filtro e de linha.

1. Contexto de Filtro: tabela cliente está filtrando as vendas e o total.
2. Contexto de linha: para chegar no valor total é executado o cálculo linha por linha da tabela de vendas.

Calculando a Quantidade de Vendas

Clique em cima da tabela de vendas ->nova medida.

Digite o cálculo abaixo:

Qtd Vendas = COUNTROWS (Vendas)

Criando Tabela de Medidas

É uma boa prática criar uma tabela de medidas para organizar os seus cálculos.

Na Página inicial, execute os passos abaixo:

1. Clique em inserir dados
2. Coloque o nome de medidas
3. Clique em carregar

Isso irá criar uma tabela, e para colocar as medidas dentro dessa tabela, vá em modelagem, e arraste as medidas para dentro da tabela.

A modelagem é um dos principais pontos de atenção na análise de dados.

Não tente corrigir problemas de modelagem, com cálculos avançados, dedique-se para criar o melhor modelo para o seu projeto.

Calculando o valor da Meta

Agora clique com botão direito em cima da tabela de medidas que você criou, e selecione nova medida.

Meta Total = SUM(Metas[ValorMeta])

Para formatar uma medida clique em cima da medida e na barra de ferramentas, faça a formatação.

Vamos falar sobre Relacionamentos

Nosso base de dados é composta por um tabelão.

Chamamos de tabelão, uma tabela desnormalizada que contém diversas colunas com categorias diferentes. O correto é organizarmos esse tabelão, separando as informações em novas tabelas, fato e dimensão.

O que são Tabelas Fato e Dimensão?

Tabelas Fato: movimentações, eventos/registros históricos (geralmente tem uma data atrelada)

• Geralmente uso em Valores (números)

• Quanto menos campos descritivos eu deixar na fato, melhor. Eles devem sempre estar nas tabelas dimensão.

Exemplo de fato: venda, pagamento, recebimento, demissão, admissão, produção de uma peça, envio de uma mercadoria, compra de um produto

Tablelas Dimensão: cadastro de algo, perspectivas pelas quais eu quero analisar os fatos, colunas descritivas

• Sempre devo ter um código único (que não se repete)

• Geralmente uso em Eixos ou Filtros

Exemplo de dimensão: descreve o fato, produto, cliente, loja, fornecedor, conta bancária, plano de contas, são cadastros

Após a separação das tabelas, teremos um modelo dimensional, que é o ideal para trabalhar com dados.

Existem os modelos, normalizado, desnormalizado e dimensional.

Modelo Normalizado:

Veja abaixo banco de dados da Microsoft.

Quando extraimos informações do sistema, é criado uma tabelão contendo todas as informações desse banco juntas.  Gerando um modelo desnormalizado.

 Modelo Desnormalizado: é um tabelão.

Veja no destaque que os atributos do produto por exemplo, estão dentro da tabela fato.

Modelo dimensional: neste modelo, veja que os atributos do produto, foram separados do tabelão, e agora estão dentro de uma tabela que chamada DimProduct.

Vamos a prática…

Nesse cenário não temos acesso ao banco, então extraímos um tabelão. Agora precisamos separar as informações para chegarmos no modelo dimensional.

Dica: Sempre trabalhe em parceria com TI, pois isso facilitará e muito o seu trabalho.

Criando a Dimensão Produto

Clique em Transformar Dados:

Botão direito em cima da Tabela de vendas e depois em Duplicar.

Na nova tabela dê um duplo clique para renomear para “dProduto”.

Com a tabela dProduto selecionada:

1. Escolher Colunas
2. Selecione os atributos do produto
3. Clique em ok.

Remova duplicatas do idProduto -> botão direito em cima da coluna:

Pronto… Dimensão produto criada a partir da tabela de vendas.

Caso tenha feito alguma etapa errada, clique no X destacado para step feito e refazer, isso é tipo um crtl + Z:

Lembre-se, criamos essa dimensão através da tabela de vendas, e nesse caso se você quiser ver um item no cadastro que não foi vendido, terá que buscar o cadastro de outro lugar.

Pulo do Gato!!!

Na tabela de Metas, temos a dimensão de tempo, a dimensão produto, e o fato de ter mais de 01 dimensão na tabela, é um bom indício de que se trata de uma Fato. Além disso temos o valor da meta ao longo do tempo.

Renomeie suas tabelas conforme abaixo, basta dar 02 cliques em cima do nome, ou clicar com o botão direito.

Retire os campos descritivos de produto das tabelas fato vendas e metas, lembre-se quanto menos colunas melhor.

Dica: Os campos descritivos devem sempre estar na tabela dimensão.

Exclua as colunas NomeProduto, idCategoriaProduto e CategoriaProduto da fVendas -> selecione as 3 -> botão direito -> Remover Colunas

Seguindo os mesmos passos, remova a coluna CategoriaProduto da tabela fMetas.

Tratamento da tabela dCliente

Lembra que a tabela com o tipo estava separada das demais informações do cliente.

O objetivo aqui é levar o tipo do cliente para dentro da tabela cliente. Para isso, no Power Query:

1. Clique na tabela dCliente
2. Em página inicial
3. Opção: mesclar consultas
4. Na aba aberta escolha os campos que correspondem entre si, nesse caso idTipoCliente.
5. Clique em OK.

 Em seguida:

1. Clique em expandir
2. Selecione as colunas que deseja
3. Desmarque a opção: “Use o nome da coluna original como prefixo”
4. Clique em OK.

É interessante destacar que mesclar consultas é semelhante ao PROCV do Excel, mas com vantagem de poder escolher quantas colunas eu quiser para expandir, pois no Excel se você quisesse carregar as 02 colunas idTipoCliente e TipoCliente, você teria que fazer a fórmula 02 vezes, 01 para cada coluna.

Resultado do mesclar consultas.

Agora, na dTipoCliente >> botão direito >> desmarque a opção habilitar carga

Lembrando que chamamos de carga, o envio dos dados para o seu modelo, ou seja, envio da tabela do power query para sua aba de modelagem.

Clique em Fechar e aplicar no power query.

Assim deve ficar o seu modelo.

Tabela Calendário em DAX

Sim, nós vamos criar uma tabela utilizando a linguagem DAX, eu sei que eu disse antes que não é adequado criar tabelas utilizando a linguagem DAX, porém a dCalendário é uma exceção.

Mas o que é uma dCalendário?

Calendário é uma tabela que contém 01 data por linha, ela é necessária para filtrar a tabela fato considerando o atributo de tempo.

Clique na guia modelagem >> Nova Tabela

Digite na barra de fórmulas:

dCalendario = CALENDARAUTO()

A função calendarauto criará uma tabela com os dias existentes entre primeiro dia do ano mínimo e último dia do ano máximo do seu modelo.

Clique em modelagem no destaque e relacione as datas:

1. Date da tabela Calendário com mês da tabela fMetas, lembrando que apesar da nomenclatura mês, a nossa tabela fMetas contém nessa coluna o primeiro dia de cada mês em formato de data (01/01/2020, 01/02/2020, etc…), isso facilita na hora de criar esse relacionamento.
   
2. Date da tabela Calendário com DataVenda da tabela fVendas.

Dica: Escolhemos a data da venda nesse caso pois estamos analisando vendas, mas esse relacionamento pode variar de acordo a regra de negócio.

Crie um visual matriz com os seguintes campos:

1. Selecione o visual de Matriz
2. No campo linhas coloque a data da tabela dCalendário
3. Clique no X destacado para remover os campos, deixando apenas o mês
4. Coloque no campo linhas a categoria do produto da tabela dProduto
5. Coloque no campo valores as medidas, Valor Total e Meta Total
6. Se tiver alguns valores na cor branca clique no rolinho
7. Na opção subtotais, escolha cor da fonte, preto.
8. Note que o valor da meta está igual para todas as categorias, isso é porque ainda não relacionamos a dProduto com a fMetas

Relacione dProduto e fMetas.

Ao criar esse relacionamento, abre uma janelinha nova… ?

Essa janela nova é onde podemos ver as propriedades e ajustar um relacionamento. Ao relacionar fMetas com dProduto, estamos ligando tabelas com granularidades diferentes, pois a tabela produto tem 01 produto para cada linha e a categoria se repete, já na tabela de metas eu não tenho o grão de produto, somente categoria que se repete também.

Lembra que eu disse que a tabela dimensão deve ter sempre um código único?

Granularidade: É nível de detalhe que eu tenho nas tabelas.

Entenda melhor:

Na janela aberta faça as seguintes configurações:

1. Selecione os campos correspondentes entre as tabelas que você esta relacionando
2. Note que a cardinalidade é de muitos:muitos, conforme acabamos de explicar. Ainda tem dúvidas sobre esse assunto, aqui tem artigo inteirinho falando só sobre relacionamentos. [link artigo sobre relacionamentos]
3. A direção do filtro sempre deve ser, dimensão filtra fato. Veja que a opção dProduto filtra fMetas.
4. Nesse caso, por padrão o relacionamento fica inativo, então marque a opção de ativar.
5. E por último, clique em OK.

Note na modelagem o asterisco, na ponta de cada seta, ele é um indicativo do relacionamento muitos para muitos.

Comparativo Venda x Meta

Clique em nova medida, digite o cálculo abaixo.

Vendas X Meta = DIVIDE ( [Valor Total] , [Meta Total] )

Coloque a medida na tabela que estamos trabalhando.

Deve ficar assim:

Vamos relembrar… Quais são as 04 formas de fazer o famoso PROCX do Excel no Power BI?

1. Mesclando consultas: aplicamos isso no power query para tratar a tabela de cliente
2. Usando a função RELATED: vamos utilizar a seguir
3. Criando relacionamentos
4. Usando a função LOOKUPVALUE. Não falarei sobre ela aqui, mas você pode saber mais sobre ela lendo o artigo onde falei apenas sobre isso.

Usando a Função Related

No nosso modelo a tabela de custos está separada e nós precisamos calcular o custo total.

Como faremos isso?!

O primeiro passo é importar a tabela de custos.

Vá em Obter Dados:

1. Selecione a tabela “CustoProduto”
2. Clique em transformar dados

Entendendo o negócio:

O Custo é um atributo do produto nesse caso, pois ele não varia, por isso vamos colocá-lo dentro da tabela de produto.

No Power Query:

1. Clique na tabela “dProduto”
2. Selecione mesclar consultas
3. Selecione a coluna “idProduto” da tabela “dProduto”
4. Selecione a coluna “idProduto” da tabela “CustoProduto”
5. Clique em OK.

A seguir, vamos expandir os dados que precisamos. Com a tabela dProduto selecionada:

1. Clique para expandir
2. Selecione o “CustoUnitario”
3. Clique em OK.

Agora já temos o custo na tabela dProduto, então não iremos precisar da tabela CustoProduto, assim, vamos desabilitar a carga dela.

Clique em cima da tabela “CustoProduto”, botão direito, desmarque a opção “habilitar carga”.

Clique em fechar e aplicar.

Clique na tabela de medidas -> nova medida.

Cálculo:

Custo Total = SUMX ( fVendas, fVendas[Quantidade] * RELATED (dProduto[CustoUnitario])

Dica:
o RELATED só pode usado quando há relacionamento entre as tabelas informadas no cálculo. Quando não há relacionamento, a opção é utilizar a função LOOKUPVALUE.

Tabela 1 = fVendas[Quantidade]

Tabela 2 = dProduto[CustoUnitario]

Agora coloque a medida Custo Total na tabela.

Função CALCULATE

Tá acompanhando… muito conteúdo né?! ?

A CALCULATE é a função mais importante da linguagem DAX. Ela é capaz de modificar o contexto original (aplicando filtros ou utilizando funções que modificam o contexto)

– Sintaxe: CALCULATE( [Medida], Filtro 1, Filtro 2, … Filtro N)

  – Funções de Tabela:

• ALL: retorna uma tabela removendo/ignorando os filtros aplicados na coluna/tabela passado como argumento

• ALLSELECTED: retorna uma tabela removendo os filtros aplicados na coluna/tabela passada como argumento, porém respeito os filtros externos

• VALUES: retorna uma tabela considerando/devolvendo os filtros aplicados na coluna/tabela passado como argumento.

Crie uma página nova ai, e vamos iniciar os trabalhos…

Deixa a tabela conforme abaixo, mostrando o valor total por categoria de produtos.

Agora imagine o seguinte cenário:

Se eu quiser comparar o valor total de vendas das categorias, mas considerando somente o tipo restaurante.

Clique na tabela medidas -> nova medida.

Cálculo:

Valor Restaurante = CALCULATE ( [Valor Total] , dCliente[TipoCliente] = “Restaurante” )

Coloque a medida Valor Restaurante na tabela.

Vamos entender o contexto…

1. Quais filtros estão influenciando no valor 760,93?
2. O valor 760,93 corresponde ao filtro da categoria = Legumes
3. O valor 760,393 também corresponde ao filtro aplicado no cálculo, TipoCliente = “Restaurante”

E como podemos calcular o percentual de vendas por categoria…

As contas no Power BI são feitas de acordo com o contexto, onde cada linha é um contexto.

Para efetuar o cálculo do percentual, precisamos primeiro fixar o valor da venda em cada contexto.

Vá em nova medida:

Valor Total ALL Produto = CALCULATE ( [Valor Total] , ALL (dProduto) )

O All nesse caso está removendo o filtro da tabela dProduto, ou seja as vendas não estão sendo mais filtradas de acordo com a categoria, veja abaixo como o valor se repete nas linhas.

Agora é só dividir o Valor Total pelo Valor Total Fixo

Cálculo:

% Valor  = DIVIDE ( [Valor Total] , [Valor Total Fixo] )

Dica: sempre que for dividir no Power BI, use a função DIVIDE, ela é mais segura que a “/” para fazer divisões.

Para formatar como percentual, clique na medida criada, em seguida na barra de ferramentas faça a formatação.

Vamos analisar o que acontece se eu colocar um slicer(filtro) com o tipo de cliente na tela.

1. Selecione o visual slicer, e arraste para dentro dele o “TipoCliente” da tabela cliente
2. Selecione o TipoCliente = Mercado
3. Perceba que o valor mudou de 4.764,46 para 3.682,93, ou seja a medida Total Fixo está respeitando o filtro de mercado, pois pedimos para limpar apenas o filtro da dimensão produto, e o tipo de cliente está na tabela Cliente.

O que acontece então se ao invés de remover o filtro da tabela de Produto, eu removesse o filtro da tabela inteira de vendas.

Assim:

1. Substitua ALL(dProduto) por ALL(fVendas).
2. Mantenha o tipo de cliente “Mercado” no filtro
3. Note que voltamos a ter o valor total de vendas, independente de qualquer filtro. Ou seja, remover o contexto de filtro da tabela de vendas, é a mesma coisa que remover o filtro de todas as dimensões.

Diferença entre ALL X ALLSELECTED

Primeiro organize a tabela da seguinte forma:

1. No filtro Slicer: coloque substitua o tipo de cliente por Nome do Produto (tabela dProduto)
2. No visual de matriz: substitua a categoria do produto por nome do produto.

Renomeie a medida Valor Total Fixo para Valor Total ALL Produto. Dessa forma:

Valor Total ALL Produto = CALCULATE( [Valor Total], ALL(dProduto)

Renomeie a medida % Valor para Valor Total ALL Produto. Dessa forma:

% Valor ALL = DIVIDE([Valor Total], [Valor Total All Produto])

Crie uma nova medida:

Valor Total ALLSELECTED Produto = CALCULATE ( [Valor Total] , ALLSELECTED (dProduto) )

Inclua a medida na tabela.

Agora veja o comportamento de ALL e ALLSELECTED:

– Sem nenhum filtro aplicado, os valores são iguais

– Com filtro aplicado, os valores mudam.

1. Veja selecionamos 04 produtos

2. O valor total dos produtos selecionados, é o valor considerado também no ALLSELECTED.

3. O valor total com ALL continua considerando todos os produtos, ou seja, desconsidera também o filtro externo aplicado (visual slicer)

Ao criarmos o percentual com o ALLSELECTED, veremos um valor de 100%.

Crie a medida:

% Valor ALLSELECTED = DIVIDE ( [Valor Total] , [Valor ALLSELECTED Produto] )

Inclua a medida na tabela, e vamos ver como ficou.

Distinctcount x ALL x VALUES

Crie uma nova página

E deixa a matriz apenas com a categoria do produto.

Nova Medida:

Clientes = DISTINCTCOUNT ( fVendas[idCliente] )

Entenda:

Queremos saber quantos clientes distintos (diferentes) compraram por categoria.

O idCliente existe em 02 lugares, na tabela Cliente e na tabela fVendas, porém se eu quero saber os clientes que compraram, então devo considerar idCliente da tabela de vendas.

Lembre-se: eu disse anteriormente que o total nem sempre é a soma dos valores acima dele, esse um caso. A base contém apenas 6 clientes, portanto total está correto.

Do 6 clientes que contém na base, 3 compraram frutas, 4 compraram verduras e todos compraram legumes.

Vamos criar mais algumas medidas:

Clientes ALL = COUNTROWS ( ALL (fVendas [idCliente] )

Clientes VALUES = COUNTROWS ( VALUES ( fVendas [idCliente] )

Inclua essas novas medidas, na tabela.

Qual a diferença entre DISTINCOUNT x ALL X VALUES

DISTINCOUNT E VALUES, são iguais, retornam o mesmo valor. Ambas respeitam o contexto de filtro, que nesse caso é a “CategoriaProduto”.

ALL, repete o total de clientes para todas as linhas, isso porque essa função remove o filtro, ou seja remove o contexto.

Diluir a Meta por Produto

Deixe o seu visual de matriz formatado conforme abaixo:

1. Selecione o visual de matriz
2. Em linhas: coloque “CategoriaProduto” e “NomeProduto”
3. Em valores: coloque as medidas, Valor Total e Meta Total
4. Clique no garfinho destacado, para expandir toda a tabela
5. Note que a meta não está sendo diluído por produto, isso porque a tabela que nos foi fornecida contém metas apenas por categoria.

Como podemos fazer para diluir a meta no nível de produto? ?

A solução é criar um rateio, e este pode ser definido de acordo com a regra de negócio, aqui nesse caso faremos com base no percentual de vendas.

Como isso seria feito no Excel, vamos tomar por exemplo o produto Kiwi:

1. Valor Venda Kiwi = 685,27
2. Valor Total da Categoria Frutas = 1.870,97
3. Valor Total Considerando todas as vendas = 6.250,00

Então…

(1 – Valor Venda Kiwi / 2 – Valor Total da Categoria Frutas)*3- Valor Total Considerando todas as vendas

Mas e no Power BI?

Vamos criar uma medida:

Aqui vamos utilizar as variáveis (VAR) para deixar mais organizado.

Inclua a medida na tabela.

E se quisermos analisar valor por Data de Entrega?

Vamos deixar nosso visual de matriz, apenas com o mês e quantidade.

Para sabermos qual foi a quantidade entregue, temos que criar um relacionamento entre a data da dCalendario e a data de entrega da fVendas.

1. Faça o relacionamento
2. Note que a linha está pontilhada, isso indica que o relacionamento está inativo.

Nesse caso, está inativo porque já existe um relacionamento entre as tabelas fMetas e dCalendario (data dCalendario -> data da venda) e podemos ter apenas um relacionamento existente entre 02 tabelas.

OK! Mas e agora? Vamos utilizar a função USERELATIONSHIP para ativar esse relacionamento inativo. ?

Crie a medida:

Quantidade Entregue =

CALCULATE(   
SUM(fVendas[Quantidade]); 
USERELATIONSHIP(fVendas[DataEntrega];
dCalendario[Date]))

E voilá… temos nossa Quantidade Entregue.

Bom galera, é isso… Espero que tenham gostado.

Foi um baita revisão para vocês, sobre todos os aspectos, desde extração, estruturação, modelagem e DAX.

Abraço,
Leonardo.