Quando aprende a utilizar o Power Pivot pela primeira vez, a maioria dos utilizadores descobre que o poder real é agregar ou calcular um resultado de alguma forma. Se os seus dados tiverem uma coluna com valores numéricos, pode agregar facilmente ao selecioná-la numa Tabela Dinâmica ou numa Lista de Campos do Power View. Por natureza, uma vez que é numérica, será automaticamente somada, média, contada ou qualquer tipo de agregação que selecionar. Isto é conhecido como uma medida implícita. As medidas implícitas são ótimas para uma agregação rápida e fácil, mas têm limites e esses limites podem quase sempre ser ultrapassados com medidas explícitas e colunas calculadas.
Primeiro, vamos ver um exemplo em que utilizamos uma coluna calculada para adicionar um novo valor de texto para cada linha numa tabela denominada Produto. Cada linha na tabela Produto contém todos os tipos de informações sobre cada produto que vendemos. Temos colunas para Nome do Produto, Cor, Tamanho, Preço do Revendedor, etc.. Temos outra tabela relacionada denominada Categoria do Produto que contém uma coluna ProductCategoryName. O que queremos é que cada produto na tabela Produto inclua o nome da categoria de produto da tabela Categoria do Produto. Na nossa tabela Produto, podemos criar uma coluna calculada denominada Categoria do Produto da seguinte forma:
A nossa nova fórmula Product Category utiliza a função DAX RELATED para obter valores da coluna ProductCategoryName na tabela categoria de produto relacionada e, em seguida, introduz esses valores para cada produto (cada linha) na tabela Produto.
Este é um excelente exemplo de como podemos utilizar uma coluna calculada para adicionar um valor fixo para cada linha que podemos utilizar mais tarde na área LINHAS, COLUNAS ou FILTROS da Tabela Dinâmica ou num relatório do Power View.
Vamos criar outro exemplo em que queremos calcular uma margem de lucro para as nossas categorias de produtos. Este é um cenário comum, mesmo em muitos tutoriais. Temos uma tabela Vendas no nosso modelo de dados que tem dados de transação e existe uma relação entre a tabela Vendas e a tabela Categoria de Produto. Na tabela Vendas, temos uma coluna que tem valores de vendas e outra coluna que tem custos.
Podemos criar uma coluna calculada que calcula um valor de lucro para cada linha ao subtrair valores na coluna COGS dos valores na coluna SalesAmount, da seguinte forma:
Agora, podemos criar uma Tabela Dinâmica e arrastar o campo Categoria do Produto para COLUNAS e o nosso novo campo Lucro para a área VALORES (uma coluna numa tabela no PowerPivot é um Campo na Lista de Campos da Tabela Dinâmica). O resultado é uma medida implícita denominada Soma do Lucro. É uma quantidade agregada de valores da coluna de lucro para cada uma das diferentes categorias de produtos. O nosso resultado tem o seguinte aspeto:
Neste caso, Lucro só faz sentido como um campo em VALORES. Se colocássemos Lucro na área COLUNAS, a nossa Tabela Dinâmica teria o seguinte aspeto:
O nosso campo Lucro não fornece informações úteis quando é colocado nas áreas COLUNAS, LINHAS ou FILTROS. Só faz sentido como um valor agregado na área VALORES.
O que fizemos foi criar uma coluna denominada Lucro que calcula uma margem de lucro para cada linha na tabela Vendas. Em seguida, adicionámos Lucro à área VALORES da nossa Tabela Dinâmica, criando automaticamente uma medida implícita, em que um resultado é calculado para cada uma das categorias de produtos. Se estiver a pensar que calculámos o lucro para as nossas categorias de produtos duas vezes, está correto. Calculámos primeiro um lucro para cada linha na tabela Vendas e, em seguida, adicionámos Lucro à área VALORES onde foi agregado para cada uma das categorias de produtos. Se também estiver a pensar que não precisamos realmente de criar a coluna calculada Lucro, também está correto. Mas, como podemos calcular o nosso lucro sem criar uma coluna calculada Lucro?
Lucro, seria realmente melhor calculado como uma medida explícita.
Por agora, vamos deixar a nossa coluna calculada Lucro na tabela Vendas e Categoria de Produto em COLUNAS e Lucro em VALORES da nossa Tabela Dinâmica, para comparar os nossos resultados.
Na área de cálculo da nossa tabela Vendas, vamos criar uma medida denominada Lucro Total (para evitar conflitos de nomenclatura). No final, produzirá os mesmos resultados que fizemos anteriormente, mas sem uma coluna calculada Lucro.
Primeiro, na tabela Vendas, selecionamos a coluna SalesAmount e, em seguida, clicamos em Soma Automática para criar uma medida Soma explícita de SalesAmount. Lembre-se de que uma medida explícita é uma medida que criamos na área de cálculo de uma tabela no Power Pivot. Fazemos o mesmo para a coluna COGS. Vamos mudar o nome destes MontantesDeVendas Totais e Total de COGS para facilitar a sua identificação.
Em seguida, criamos outra medida com esta fórmula:
Total Profit:=[ Total SalesAmount] - [Total DE COGS]
Nota: Também poderíamos escrever a nossa fórmula como Total Profit:=SUM([SalesAmount]) - SUM([COGS]), mas ao criar medidas de Total SalesAmount e Total COGS separadas, também podemos utilizá-las na nossa Tabela Dinâmica e podemos utilizá-las como argumentos em todos os tipos de outras fórmulas de medida.
Depois de alterarmos o formato da nova medida Lucro Total para moeda, podemos adicioná-la à nossa tabela dinâmica.
Pode ver que a nossa nova medida Lucro Total devolve os mesmos resultados que criar uma coluna calculada Lucro e, em seguida, colocá-la em VALORES. A diferença é que a nossa medida Lucro Total é muito mais eficiente e torna o nosso modelo de dados mais limpo e mais magro porque estamos a calcular na altura e apenas para os campos que selecionamos para a nossa Tabela Dinâmica. Afinal de contas, não precisamos dessa coluna calculada Lucro.
Porque é que esta última parte é importante? As colunas calculadas adicionam dados ao modelo de dados e os dados retomam a memória. Se atualizarmos o modelo de dados, os recursos de processamento também são necessários para recalcular todos os valores na coluna Lucro. Não precisamos de utilizar recursos como este porque queremos realmente calcular o nosso lucro quando selecionamos os campos para os quais queremos Lucro na Tabela Dinâmica, como categorias de produtos, região ou datas.
Vejamos outro exemplo. Uma em que uma coluna calculada cria resultados que, à primeira vista, parecem corretos, mas...
Neste exemplo, queremos calcular os valores de vendas como uma percentagem do total de vendas. Criamos uma coluna calculada denominada % das Vendas na nossa tabela Vendas, da seguinte forma:
A nossa fórmula indica: para cada linha na tabela Vendas, divida o valor na coluna SalesAmount pelo total SOMA de todos os montantes na coluna SalesAmount.
Se criarmos uma Tabela Dinâmica e adicionarmos Categoria de Produto a COLUNAS e selecionarmos a nossa nova coluna % de Vendas para colocá-la em VALORES, obtemos uma soma total de % de Vendas para cada uma das nossas categorias de produtos.
Ok. Parece bom até agora. No entanto, vamos adicionar uma segmentação de dados. Adicionamos Ano do Calendário e, em seguida, selecionamos um ano. Neste caso, selecionamos 2007. Isto é o que temos.
À primeira vista, isto ainda pode parecer correto. No entanto, as nossas percentagens devem totalizar 100%, porque queremos saber a percentagem de vendas totais de cada uma das nossas categorias de produtos para 2007. Então, o que correu mal?
A nossa coluna % de Vendas calculou uma percentagem para cada linha que é o valor na coluna SalesAmount dividida pela soma total de todos os valores na coluna SalesAmount. Os valores numa coluna calculada são fixos. São um resultado imutável para cada linha na tabela. Quando adicionámos % de Vendas à nossa Tabela Dinâmica, esta foi agregada como uma soma de todos os valores na coluna SalesAmount. Essa soma de todos os valores na coluna % de Vendas será sempre de 100%.
Sugestão: Certifique-se de que lê Contexto em Fórmulas DAX. Fornece uma boa compreensão do contexto ao nível da linha e do contexto de filtro, que é o que descrevemos aqui.
Podemos eliminar a nossa coluna calculada % de Vendas porque não nos vai ajudar. Em vez disso, vamos criar uma medida que calcula corretamente a nossa percentagem de vendas totais, independentemente de quaisquer filtros ou segmentações de dados aplicados.
Lembra-se da medida TotalSalesAmount que criámos anteriormente, aquela que soma simplesmente a coluna SalesAmount? Utilizámo-la como argumento na nossa medida Lucro Total e vamos utilizá-la novamente como argumento no nosso novo campo calculado.
Sugestão: A criação de medidas explícitas como Total SalesAmount e Total COGS não são apenas úteis numa tabela dinâmica ou relatório, mas também são úteis como argumentos noutras medidas quando precisa do resultado como argumento. Isto torna as suas fórmulas mais eficientes e fáceis de ler. Esta é uma boa prática de modelação de dados.
Criamos uma nova medida com a seguinte fórmula:
% do Total de Vendas:=([Total de SalesAmount]) /CALCULATE([Total SalesAmount], ALLSELECTED())
Esta fórmula indica: divida o resultado de Total SalesAmount pela soma total de SalesAmount sem quaisquer filtros de coluna ou linha que não os definidos na Tabela Dinâmica.
Sugestão: Certifique-se de que lê sobre as funções CALCULATE e ALLSELECTED na Referência DAX.
Agora, se adicionarmos a nossa nova % do Total de Vendas à Tabela Dinâmica, obtemos:
Parece melhor. Agora, a nossa % do Total de Vendas para cada categoria de produto é calculada como uma percentagem do total de vendas do ano de 2007. Se selecionarmos um ano diferente ou mais de um ano na segmentação CalendarYear, obtemos novas percentagens para as nossas categorias de produtos, mas o nosso total geral ainda é de 100%. Também podemos adicionar outras segmentações de dados e filtros. A nossa medida % do Total de Vendas produzirá sempre uma percentagem do total de vendas, independentemente de quaisquer segmentações de dados ou filtros aplicados. Com as medidas, o resultado é sempre calculado de acordo com o contexto determinado pelos campos em COLUNAS e LINHAS e por quaisquer filtros ou segmentações de dados aplicados. Este é o poder das medidas.
Seguem-se algumas diretrizes para o ajudar a decidir se uma coluna calculada ou uma medida é adequada para uma necessidade de cálculo específica:
Utilizar colunas calculadas
-
Se quiser que os seus novos dados apareçam em LINHAS, COLUNAS ou EM FILTROS numa Tabela Dinâmica ou num Eixo, LEGENDA ou MOSAICO POR numa visualização do Power View, tem de utilizar uma coluna calculada. Tal como as colunas regulares de dados, as colunas calculadas podem ser utilizadas como um campo em qualquer área e, se forem numéricas, também podem ser agregadas em VALORES.
-
Se quiser que os novos dados sejam um valor fixo para a linha. Por exemplo, tem uma tabela de datas com uma coluna de datas e quer outra coluna que contenha apenas o número do mês. Pode criar uma coluna calculada que calcula apenas o número do mês a partir das datas na coluna Data. Por exemplo, =MÊS('Data'[Data]).
-
Se quiser adicionar um valor de texto para cada linha a uma tabela, utilize uma coluna calculada. Os campos com valores de texto nunca podem ser agregados em VALORES. Por exemplo, =FORMAT('Date'[Date],"mmmm") dá-nos o nome do mês para cada data na coluna Data na tabela Data.
Utilizar medidas
-
Se o resultado do cálculo depender sempre dos outros campos que selecionar numa Tabela Dinâmica.
-
Se precisar de efetuar cálculos mais complexos, como calcular uma contagem com base num filtro de algum tipo ou calcular uma variação ou um ano, utilize um campo calculado.
-
Se quiser manter o tamanho do livro no mínimo e maximizar o desempenho, crie o maior número possível de cálculos. Em muitos casos, todos os seus cálculos podem ser medidas, reduzindo significativamente o tamanho do livro e acelerando o tempo de atualização.
Tenha em atenção que não há nada de errado em criar colunas calculadas como fizemos com a nossa coluna Lucro e, em seguida, agregá-la numa Tabela Dinâmica ou relatório. Na verdade, é uma forma muito boa e fácil de aprender e criar os seus próprios cálculos. À medida que compreende estas duas funcionalidades extremamente poderosas do Power Pivot, vai querer criar o modelo de dados mais eficiente e preciso possível. Espero que o que aprendeu aqui ajude. Existem outros recursos realmente excelentes que podem ajudá-lo também. Eis apenas alguns: Contexto em Fórmulas DAX, Agregações no Power Pivot e Centro de Recursos DAX. Apesar de ser um pouco mais avançado e direcionado para profissionais de contabilidade e finanças, o exemplo de Modelação e Análise de Dados de Lucros e Perdas com o Microsoft Power Pivot no Excel é carregado com excelentes modelos de dados e exemplos de fórmulas.
