Quando usar Colunas Calculadas e Campos Calculados

Ao aprender a usar o Power Pivot pela primeira vez, a maioria dos usuários descobre que o verdadeiro poder está em agregar ou calcular um resultado de alguma forma. Se os seus dados tiverem uma coluna com valores numéricos, você poderá agregá-los facilmente selecionando-os em uma Tabela Dinâmica ou em uma Lista de Campos do Power View. Por natureza, por ser numérico, ele será automaticamente somado, calculado, contado ou qualquer tipo de agregação que você selecionar. Isso é conhecido como uma medida implícita. Medidas implícitas são ótimas para agregação rápida e fácil, mas têm limites, e esses limites quase sempre podem ser superados com medidasexplícitas e colunas calculadas.

Vejamos primeiro um exemplo em que usamos uma coluna calculada para adicionar um novo valor de texto para cada linha em uma tabela chamada 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 chamada Categoria de 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 de Produto. Em nossa tabela Produto, podemos criar uma coluna calculada chamada Categoria de Produto da seguinte forma:

Nossa nova fórmula de Categoria de Produto usa a função DAX RELATED para obter valores da coluna ProductCategoryName na tabela Categoria de Produto relacionada e insere esses valores para cada produto (cada linha) na tabela Produto.

Este é um ótimo exemplo de como podemos usar uma coluna calculada para adicionar um valor fixo para cada linha que podemos usar posteriormente na área LINHAS, COLUNAS ou FILTROS da Tabela Dinâmica ou em um relatório do Power View.

Vamos criar outro exemplo em que queremos calcular uma margem de lucro para nossas categorias de produtos. Este é um cenário comum, mesmo em muitos tutoriais. Temos uma tabela Vendas em nosso modelo de dados que tem dados de transação e há uma relação entre a tabela Vendas e a tabela Categoria de Produto. Na tabela Vendas, temos uma coluna que contém valores de vendas e outra coluna que contém custos.

Podemos criar uma coluna calculada que calcula um valor de lucro para cada linha subtraindo os valores na coluna CPV dos valores na coluna SalesAmount, da seguinte maneira:

Agora, podemos criar uma Tabela Dinâmica e arrastar o campo Categoria do Produto para COLUNAS, e nosso novo campo Lucro para a área VALORES (uma coluna em uma tabela no PowerPivot é um Campo na Lista de Campos da Tabela Dinâmica). O resultado é uma medida implícita chamada Soma do Lucro. É uma quantidade agregada de valores da coluna de lucro para cada uma das diferentes categorias de produtos. Nosso resultado fica assim:

Nesse caso, Lucro só faz sentido como um campo em VALORES. Se colocássemos Lucro na área COLUNAS, nossa Tabela Dinâmica ficaria assim:

Nosso campo Lucro não fornece nenhuma informação útil quando é colocado nas áreas COLUNAS, LINHAS ou FILTROS. Ele só faz sentido como um valor agregado na área VALORES.

O que fizemos foi criar uma coluna chamada Lucro que calcula uma margem de lucro para cada linha na tabela Vendas. Em seguida, adicionamos Lucro à área VALORES de nossa Tabela Dinâmica, criando automaticamente uma medida implícita, onde um resultado é calculado para cada uma das categorias de produtos. Se você está pensando que realmente calculamos o lucro para nossas categorias de produtos duas vezes, você está correto. Primeiro, calculamos um lucro para cada linha na tabela Vendas e, em seguida, adicionamos Lucro à área VALORES, onde foi agregado para cada uma das categorias de produtos. Se você também está pensando que realmente não precisávamos criar a coluna calculada Lucro, você também está correto. Mas, como então calculamos nosso lucro sem criar uma coluna calculada de lucro?

Lucro, seria realmente melhor calculado como uma medida explícita.

Por enquanto, vamos deixar nossa coluna calculada Lucro na tabela Vendas e Categoria Produto em COLUNAS e Lucro em VALORES da nossa Tabela Dinâmica, para comparar nossos resultados.

Na área de cálculo da nossa tabela Vendas, vamos criar uma medida chamada Lucro Total (para evitar conflitos de nomenclatura). No final, ele produzirá os mesmos resultados que fizemos antes, mas sem uma coluna calculada Lucro.

Primeiro, na tabela Vendas, selecionamos a coluna SalesAmount e clicamos em AutoSoma para criar uma medida explícita de Soma de SalesAmount . Lembre-se de que uma medida explícita é aquela que criamos na área de cálculo de uma tabela no Power Pivot. Fazemos o mesmo para a coluna COGS. Vamos renomeá-los como Total SalesAmount e Total COGS para facilitar a identificação.

Em seguida, criamos outra medida com esta fórmula:

Total Profit:=[Total SalesAmount] - [Total COGS]

Depois de alterar o formato da nossa nova medida de Lucro Total para moeda, podemos adicioná-la à nossa Tabela Dinâmica.

Você pode ver que nossa nova medida de Lucro Total retorna os mesmos resultados que criar uma coluna calculada de Lucro e depois colocá-la em VALORES. A diferença é que nossa medida de Lucro Total é muito mais eficiente e torna nosso modelo de dados mais limpo e enxuto, pois estamos calculando no momento e apenas para os campos que selecionamos para nossa Tabela Dinâmica. Afinal, não precisamos dessa coluna calculada de lucro.

Por que esta última parte é importante? As colunas calculadas adicionam dados ao modelo de dados, e os dados ocupam memória. Se atualizarmos o modelo de dados, os recursos de processamento também serão necessários para recalcular todos os valores na coluna Lucro. Na verdade, não precisamos usar recursos como esse porque realmente queremos calcular nosso lucro ao selecionar os campos para os quais queremos Lucro na Tabela Dinâmica, como categorias de produtos, região ou por datas.

Vejamos outro exemplo. Um em que uma coluna calculada cria resultados que, à primeira vista, parecem corretos, mas....

Neste exemplo, queremos calcular os valores de vendas como uma porcentagem das vendas totais. Criamos uma coluna calculada chamada % of Sales em nossa tabela Sales, desta forma:

Nossa fórmula afirma: Para cada linha na tabela Vendas, divida o valor da coluna ValorVendas pelo SOMA total de todos os valores na coluna ValorVendas.

Se criarmos uma Tabela Dinâmica e adicionarmos Categoria de Produto a COLUNAS e selecionarmos nossa nova coluna % de Vendas para colocá-la em VALORES, obteremos uma soma total de % de Vendas para cada uma de nossas categorias de produtos.

Está bem. Isso parece bom até agora. Mas, vamos adicionar uma segmentação. Adicionamos o Calendar Year e selecionamos um ano. Nesse caso, selecionamos 2007. Isto é o que temos.

À primeira vista, isso ainda pode parecer correto. Mas, nossas porcentagens devem realmente totalizar 100%, porque queremos saber a porcentagem do total de vendas para cada uma de nossas categorias de produtos em 2007. Então, o que deu errado?

Nossa coluna % de Vendas calculou um percentual para cada linha, ou seja, o valor na coluna ValorVendas dividido pela soma total de todos os valores na coluna ValorVendas. Os valores em uma coluna calculada são fixos. Eles são um resultado imutável para cada linha na tabela. Quando adicionamos % das vendas à nossa Tabela Dinâmica, elas foram agregadas como a soma de todos os valores na coluna SalesAmount. Essa soma de todos os valores na coluna % of Sales será sempre 100%.

Podemos excluir nossa coluna calculada % de Vendas porque isso não nos ajudará. Em vez disso, criaremos uma medida que calcule corretamente nossa porcentagem das vendas totais, independentemente de quaisquer filtros ou segmentações aplicados.

Lembra-se da medida TotalSalesAmount que criamos anteriormente, aquela que simplesmente soma a coluna SalesAmount? Nós o usamos como um argumento em nossa medida de Lucro Total e vamos usá-lo novamente como um argumento em nosso novo campo calculado.

Criamos uma nova medida com a seguinte fórmula:

% of Total Sales:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())

Esta fórmula afirma: Divida o resultado de Total SalesAmount pela soma total de SalesAmount sem filtros de coluna ou linha além daqueles definidos na Tabela Dinâmica.

Agora, se adicionarmos nossa nova % do Total de Vendas à Tabela Dinâmica, obteremos:

Isso parece melhor. Agora, nossa % das vendas totais para cada categoria de produto é calculada como uma porcentagem das vendas totais para o ano de 2007. Se selecionarmos um ano diferente, ou mais de um ano na segmentação CalendarYear, obteremos novas porcentagens para nossas categorias de produtos, mas nosso total geral ainda será 100%. Também podemos adicionar outras segmentações e filtros. Nossa medida % do Total de Vendas sempre produzirá uma porcentagem do total de vendas, independentemente de quaisquer segmentações ou filtros aplicados. Com 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.

Aqui estão algumas diretrizes para ajudá-lo a decidir se uma coluna calculada ou uma medida é correta ou não para uma necessidade de cálculo específica:

Usar colunas calculadas

• Se você quiser que seus novos dados apareçam em LINHAS, COLUNAS ou em FILTROS em uma Tabela Dinâmica ou em um EIXO, LEGENDA ou LADO A LADO POR em uma visualização do Power View, você deve usar uma coluna calculada. Assim como colunas regulares de dados, colunas calculadas podem ser usadas como um campo em qualquer área e, se forem numéricas, também poderão ser agregadas em VALORES.
• Se você quiser que os novos dados sejam um valor fixo para a linha. Por exemplo, você tem uma tabela de data com uma coluna de datas e deseja outra coluna que contenha apenas o número do mês. Você pode criar uma coluna calculada que calcula apenas o número do mês a partir das datas na coluna Data. Por exemplo, =MONTH('Date'[Date]).
• Para adicionar um valor de texto para cada linha de uma tabela, use uma coluna calculada. Campos com valores de texto nunca podem ser agregados em VALORES. Por exemplo, =FORMAT('Date'[Date],"mmmm") fornece o nome do mês para cada data na coluna Data na tabela Data.

Usar medidas

• Se o resultado do cálculo sempre dependerá dos outros campos que você selecionar em uma Tabela Dinâmica.
• Se você precisar fazer cálculos mais complexos, como calcular uma contagem com base em algum tipo de filtro ou calcular uma variação ano a ano, use um campo calculado.
• Se você quiser manter o tamanho da pasta de trabalho no mínimo e maximizar seu desempenho, crie o máximo possível de cálculos e medidas. Em muitos casos, todos os seus cálculos podem ser medidas, reduzindo significativamente o tamanho da pasta de trabalho e acelerando o tempo de atualização.

Lembre-se de que não há nada de errado em criar colunas calculadas como fizemos com nossa coluna Lucro e depois agregá-las em uma Tabela Dinâmica ou relatório. Na verdade, é uma maneira muito boa e fácil de aprender e criar seus próprios cálculos. À medida que sua compreensão desses dois recursos extremamente poderosos do Power Pivot aumentar, você desejará criar o modelo de dados mais eficiente e preciso possível. Espero que o que você aprendeu aqui ajude. Existem alguns outros recursos realmente excelentes por aí que podem ajudá-lo também. Aqui estão apenas alguns: Contexto em fórmulas DAX, agregações no Power Pivot e Centro de Recursos DAX. E, embora seja um pouco mais avançado e direcionado para profissionais de contabilidade e finanças, o exemplo de Modelagem e Análise de Dados de Lucros e Perdas com o Microsoft Power Pivot no Excel é carregado com ótimos exemplos de modelagem de dados e fórmulas.