Quando usar Colunas Calculadas e Campos Calculados

Ao aprender a usar o Power Pivot pela primeira vez, a maioria dos usuários descobre que a potência real está agregando ou calculando um resultado de alguma forma. Se seus dados tiverem uma coluna com valores numéricos, você poderá agregá-los facilmente selecionando-os em uma Tabela Dinâmica ou Na Lista de Campos do Power View. Por natureza, como é numérico, ele será automaticamente resumido, médio, 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 medidas explícitas e colunas calculadas.

Vamos primeiro examinar um exemplo em que usamos uma coluna calculada para adicionar um novo valor de texto para cada linha em uma tabela chamada Product. Cada linha na tabela Product 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 do Produto. Em nossa tabela Produto, podemos criar uma coluna calculada chamada Categoria de Produto assim:

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

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 ROWS, COLUMNS ou FILTERS 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. Esse é 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 do 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 subtraindo valores na coluna COGS de valores na coluna SalesAmount, assim:

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

Nesse caso, o Lucro só faz sentido como um campo no VALUES. Se colocássemos o Lucro na área COLUMNS, nossa Tabela Dinâmica seria assim:

Nosso campo Lucro não fornece informações úteis quando é colocado em áreas COLUMNS, ROWS ou FILTERS. Ele só faz sentido como um valor agregado na área VALUES.

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 VALUES da nossa Tabela Dinâmica, criando automaticamente uma medida implícita, em que um resultado é calculado para cada uma das categorias de produto. Se você estiver 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 VALUES em que ela foi agregada para cada uma das categorias de produto. Se você também estiver pensando que realmente não precisamos criar a coluna Lucro calculado, você também está correto. Mas, como calcular nosso lucro sem criar uma coluna calculada pelo Lucro?

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

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

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

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

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

Lucro Total:=[ Total salesamount] - [Total de COGS]

Depois de alterar o formato da 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 a criação de uma coluna calculada de Lucro e, em seguida, 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 porque estamos calculando no momento e apenas para os campos que selecionamos para nossa Tabela Dinâmica. Afinal, não precisamos dessa coluna de lucro calculado.

Por que essa última parte é importante? 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 serão necessários para recalcular todos os valores na coluna Lucro. Nós realmente não precisamos pegar recursos assim porque realmente queremos calcular nosso lucro quando selecionamos os campos para os quais queremos Lucro na Tabela Dinâmica, como categorias de produto, região ou por datas.

Vamos examinar 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 do total de vendas. Criamos uma coluna calculada chamada % das Vendas em nossa tabela Vendas, assim:

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

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

Okey. Isso parece bom até agora. Mas, vamos adicionar uma segmentação. Adicionamos o Ano Civil e selecionamos um ano. Nesse caso, selecionamos 2007. Isso é o que temos.

À primeira vista, isso ainda pode parecer correto. Mas, nossos percentuais devem realmente totalizar 100%, porque queremos saber o percentual de vendas totais para cada uma de nossas categorias de produtos para 2007. Então, o que deu errado?

Nossa coluna % of Sales calculou um percentual para cada linha que é o valor na coluna SalesAmount dividida pela soma total de todos os valores na coluna SalesAmount. Os valores em uma coluna calculada são corrigidos. Eles são um resultado imutável para cada linha na tabela. Quando adicionamos % de Vendas à nossa Tabela Dinâmica, ela foi agregada como uma soma de todos os valores na coluna SalesAmount. Essa soma de todos os valores na coluna % das Vendas sempre será de 100%.

Podemos excluir nossa coluna calculada %of Sales porque ela não vai nos ajudar. Em vez disso, criaremos uma medida que calcule corretamente nossa porcentagem de vendas totais, independentemente de quaisquer filtros ou segmentações aplicadas.

Lembra-se da medida TotalSalesAmount que criamos anteriormente, aquela que simplesmente resume a coluna SalesAmount? Usamos-o como 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:

% do total de vendas:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())

Esta fórmula afirma: divida o resultado de Total SalesAmount pela soma total de SalesAmount sem nenhum filtro de coluna ou linha diferente daqueles definidos na Tabela Dinâmica.

Agora, se adicionarmos nosso novo % de Vendas Totais à Tabela Dinâmica, obteremos:

Isso parece melhor. Agora, nosso % de Vendas Totais para cada categoria de produto é calculado como um percentual do total de vendas para o ano de 2007. Se selecionarmos um ano diferente, ou mais de um ano na segmentação CalendarYear, obteremos novos percentuais para nossas categorias de produtos, mas nosso total ainda é de 100%. Também podemos adicionar outras segmentações e filtros. Nossa medida % de Vendas Totais 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 COLUMNS e ROWS e por quaisquer filtros ou segmentações aplicadas. Esse é o poder das medidas.

Aqui estão algumas diretrizes para ajudá-lo ao decidir se uma coluna calculada ou uma medida é correta para uma determinada necessidade de cálculo:

Usar colunas calculadas

• Se você quiser que seus novos dados sejam exibidos em LINHAS, COLUNAS ou EM FILTROS em uma Tabela Dinâmica ou em um AXIS, LEGEND ou, TILE BY 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, elas também poderão ser agregadas em VALUES.

• Se você quiser que seus novos dados sejam um valor fixo para a linha. Por exemplo, você tem uma tabela de datas 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 das datas na coluna Data. Por exemplo, =MONTH('Date'[Date]).

• Se você quiser adicionar um valor de texto para cada linha a uma tabela, use uma coluna calculada. Campos com valores de texto nunca podem ser agregados em VALUES. Por exemplo, =FORMAT('Date'[Date],"mmmm") nos dá 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 selecionados em uma Tabela Dinâmica.

• Se você precisar fazer cálculos mais complexos, como calcular uma contagem com base em um filtro de algum tipo, ou calcular um ano a ano, ou variação, use um campo calculado.

• Se você quiser manter o tamanho da pasta de trabalho no mínimo e maximizar seu desempenho, crie o maior número possível de cálculos quanto possível. 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, em seguida, agregar 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 aumenta, você deseja criar o modelo de dados mais eficiente e preciso que puder. Espero que o que você aprendeu aqui ajude. Há alguns outros recursos realmente ótimos lá fora 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 Lucro e Perda com o Microsoft Power Pivot no Excel é carregado com ótima modelagem de dados e exemplos de fórmula.