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 energia real está agregando ou calculando um resultado de alguma forma. Se seus dados têm uma coluna com valores numéricos, você pode agressá-los facilmente selecionando-os em uma Tabela Dinâmica ou Power View Field List. Por natureza, como é numérico, ele será automaticamente resumido, mediado, contado ou qualquer tipo de agregação que você selecionar. Isso é conhecido como uma medida implícita. As medidas implícitas são ótimas para a agregação rápida e fácil, mas têm limites, e esses limites quase sempre podem ser ultrapassados com medidas explícitas e colunas calculadas.

Vamos primeiro ver 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 vendermos. 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 denominada Categoria de Produto como esta:

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, em seguida, insinue 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 de 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 de 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 uma quantidade 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 do Produto para COLUNAS, e nosso novo campo Lucro para a área VALUES (uma coluna em uma tabela em 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 categorias de produtos diferentes. Nosso resultado tem esta aparência:

Nesse caso, Lucro só faz sentido como um campo em VALUES. Se fossemos colocar Lucro na área COLUMNS, nossa tabela dinâmica teria esta aparência:

Nosso campo Lucro não fornece informações úteis quando ele é colocado em áreas COLUMNS, ROWS ou FILTERS. Isso 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 de nossa Tabela Dinâmica, criando automaticamente uma medida implícita, onde 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 onde ele foi agregado 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 calcularemos 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 De lucro calculada na tabela Vendas e Categoria de Produto em COLUMNS e Lucro em VALORES de nossa Tabela Dinâmica, para comparar nossos resultados.

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

Primeiro, na tabela Vendas, selecionamos a coluna SalesAmount e clique em AutoSum para criar uma soma explícita da medida 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 torná-los mais fáceis de identificar.

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

Lucro Total:=[ Total salesAmount] - [TOTAL COGS]

Depois de alterar nosso novo formato de medida de Lucro Total para moeda, podemos adicioná-lo à tabela dinâmica.

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

Por que essa ú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 de recursos assim porque queremos calcular nosso lucro quando selecionamos os campos para os que queremos Lucro na Tabela Dinâmica, como categorias de produto, região ou por 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 porcentagem do total de vendas. Criamos uma coluna calculada chamada % de Vendas em nossa tabela de Vendas, assim:

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

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 das nossas categorias de produto.

Okey. Isso parece bom até agora. Mas, vamos adicionar uma slicer. Adicionamos Ano de Calendário e, em seguida, selecionamos um ano. Nesse caso, selecionamos 2007. Isso é o que temos.

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

Nossa coluna % de Vendas calculou uma porcentagem 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 fixos. Eles são um resultado imutável para cada linha na tabela. Quando adicionamos % de Vendas à tabela dinâmica, ela foi agregada como uma soma de todos os valores na coluna SalesAmount. Essa soma de todos os valores na coluna % de Vendas sempre será 100%.

Podemos excluir nossa coluna % de Vendas calculada porque ela não nos ajudará. Em vez disso, criaremos uma medida que calcula corretamente nossa porcentagem do total de vendas, independentemente de quaisquer filtros ou slicers aplicados.

Lembre-se da medida TotalSalesAmount que criamos anteriormente, a que simplesmente resume a coluna SalesAmount? Usamo-lo 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:

% 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 quaisquer filtros de coluna ou linha diferentes daqueles definidos na Tabela Dinâmica.

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

Isso parece melhor. Agora nossa % do Total de Vendas para cada categoria de produto é calculada como uma porcentagem do total de vendas do ano de 2007. Se selecionarmos um ano diferente ou mais de um ano na slicer CalendarYear, obteremos novas porcentagens para nossas categorias de produtos, mas nosso total total ainda será 100%. Também podemos adicionar outras slicers e filtros. Nossa % da medida Total de Vendas sempre produzirá uma porcentagem do total de vendas, independentemente de quaisquer slicers ou filtros aplicados. Com as medidas, o resultado é sempre calculado de acordo com o contexto determinado pelos campos em COLUMNS e ROWS e por quaisquer filtros ou slicers aplicados. 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 apareçam em LINHAS, COLUNAS ou EM FILTROs em uma tabela dinâmica ou em um AXIS, LEGEND ou, TILE BY em uma visualização Power View, você deve usar uma coluna calculada. Assim como colunas regulares de dados, as colunas calculadas podem ser usadas como um campo em qualquer área e, se elas são numéricas, elas também podem 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 a partir 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 seu 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 por ano ou variação, use um campo calculado.

• Se você quiser manter o tamanho da sua agenda de trabalho no mínimo e maximizar seu desempenho, crie o máximo possível de seus cálculos. Em muitos casos, todos os seus cálculos podem ser medidas, reduzindo significativamente o tamanho da agenda de trabalho e acelerando o tempo de atualização.

Lembre-se de que não há nada de errado com a criação de colunas calculadas, como fizemos com nossa coluna Lucro e, em seguida, agregar-a 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 o entendimento desses dois recursos extremamente poderosos do Power Pivot aumenta, você vai querer criar o modelo de dados mais eficiente e preciso possível. Com sorte, o que você aprendeu aqui ajuda. Há outros recursos realmente excelentes por aí que também podem ajudá-lo. Aqui estão apenas alguns: Contexto em Fórmulas DAX,Agregaçãos no Power Pivote Central de Recursos do DAX. E, embora seja um pouco mais avançado e direcionado para profissionais de contabilidade e finanças, a modelagem e análise de dados de lucros e perdas com o Microsoft Power Pivot no Excel é carregada com excelentes exemplos de modelagem e fórmula de dados.