Quando usar Colunas Calculadas e Campos Calculados

Na primeira vez que aprender a usar o Power pivot, a maioria dos usuários descobre que a energia real está em agregando ou calculando um resultado de alguma maneira. Se os seus dados têm uma coluna com valores numéricos, você pode facilmente agregar-o selecionando-o em uma tabela dinâmica ou lista de campos do Power View. Por natureza, porque é numérico, ele será automaticamente somado, com média, 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 agregação rápida e fácil, mas têm limites e esses limites podem quase sempre ser superados com medidas explícitas e colunas calculadas.

Vejamos primeiro um exemplo em que usamos uma coluna calculada para adicionar um novo valor de texto para cada linha de uma tabela chamada Product. Cada linha na tabela de produtos contém todos os tipos de informações sobre cada produto que vendemos. Temos colunas para o nome do produto, a cor, o tamanho, o preço do vendedor, etc.. Temos outra tabela relacionada denominada categoria de produto que contém uma coluna ProductCategoryName. O que queremos para cada produto na tabela de produtos para incluir o nome da categoria de produto da tabela de categoria de produto. Na tabela de produtos, podemos criar uma coluna calculada denominada categoria de produto da seguinte maneira:

Coluna Categoria do produto calculada

A fórmula de categoria do novo produto usa a função Dax relacionada para obter valores da coluna ProductCategoryName na tabela de categoria de produto relacionada e, em seguida, insere esses valores para cada produto (cada linha) na tabela de produtos.

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 onde queremos calcular uma margem de lucro para nossas categorias de produtos. Esse é um cenário comum, até 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 de categoria de produtos. Na tabela de vendas, temos uma coluna com valores de vendas e outra coluna com custos.

Podemos criar uma coluna calculada que calcula um valor de lucro para cada linha subtraindo valores na coluna COGS dos valores na coluna SalesAmount, assim:

Coluna de lucro na tabela Power Pivot

Agora, podemos criar uma tabela dinâmica e arrastar o campo Categoria do produto para colunas e o 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 nomeada soma de lucro. É um valor agregado de valores da coluna lucro para cada uma das diferentes categorias de produto. Nosso resultado tem a seguinte aparência:

Exemplo de tabela dinâmica

Nesse caso, lucro somente faz sentido como um campo em valores. Se for para colocar o lucro na área de colunas, a tabela dinâmica ficaria assim:

Tabela Dinâmica sem valores úteis

Nosso campo lucro não fornece informações úteis quando ele é colocado em colunas, linhas ou filtros áreas. Isso faz sentido como um valor agregado na área VALUEs.

O que fizemos é criar uma coluna chamada lucro que calcula uma margem de lucro para cada linha da tabela Sales. Em seguida, adicionamos 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 produto. Se você estiver pensando que realmente calculamos o lucro das categorias de produto duas vezes, você está correto. Primeiro calculamos o lucro de cada linha na tabela Sales e, em seguida, adicionamos lucro à área de valores em que foi agregado para cada uma das categorias de produto. Se também estiver pensando que não precisamos realmente criar a coluna calculada de lucro, você também está correto. Mas, como faço para calcular nosso lucro sem criar uma coluna calculada de lucro?

O lucro seria ainda melhor calculado como uma medida explícita.

Por enquanto, vamos deixar nossa coluna calculada de lucro na tabela de vendas e na categoria de produto em colunas e lucro em valores de nossa tabela dinâmica para comparar nossos resultados.

Na área de cálculo da tabela de vendas, vamos criar uma medida chamada total lucro (para evitar conflitos de nomenclatura). No fim, ele produzirá os mesmos resultados que antes, mas sem uma coluna calculada de lucro.

Primeiro, na tabela Sales, selecionamos a coluna SalesAmount e clico em AutoSoma para criar uma soma explícita da medida 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. Renomearemos esses totais SalesAmount e total Cogs para facilitar a identificação.

Botão AutoSoma no Power Pivot

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

Lucro total: = [ total SalesAmount]-[total Cogs]

Observação: Também poderíamos escrever nossa fórmula como lucro total: = SUM ([SalesAmount])-SUM ([COGS]), mas ao criar medidas totais totais SalesAmount e totais de COGS totais, podemos usá-los em nossa tabela dinâmica também, e podemos usá-los como argumentos em todos os tipos de outras fórmulas de medida.

Depois de alterar o formato da nova medida do lucro total para Currency, podemos adicioná-lo à nossa tabela dinâmica.

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 valores. A diferença é que a medida do lucro total é muito mais eficiente e torna o 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 realmente dessa coluna calculada de lucro depois de tudo.

Por que esta é a ú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. Não precisamos realmente ocupar recursos como isso porque 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 dar uma olhada em outro exemplo. Um local em que uma coluna calculada cria resultados que, à primeira vista, tem uma aparência correta, mas....

Neste exemplo, queremos calcular os valores de vendas como uma porcentagem do total de vendas. Criamos uma coluna calculada chamada % das vendas na tabela Sales, assim:

Coluna % de Vendas calculadas

Nossa fórmula afirma: para cada linha da tabela Sales, divida o valor na coluna SalesAmount pela soma total de todos os valores na coluna SalesAmount.

Se criarmos uma tabela dinâmica e adicionar categoria de produto às colunas e selecionar nossa nova coluna % das vendas para colocá-la em valores, obteremos uma soma total de% das vendas para cada uma das categorias de produto.

Tabela Dinâmica mostrando a soma de % das Vendas das Categorias de Produto

Okey. Isso parece ótimo até agora. Mas vamos adicionar uma segmentação de uma segmentação. Adicionamos o ano civil e selecionamos um ano. Nesse caso, selecionamos 2007. Isso é o que obtemos.

Resultado incorreto de Soma de % das vendas na Tabela Dinâmica

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

Nossa coluna% da venda calculava 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 da tabela. Quando adicionamos % das vendas à tabela dinâmica, ela era agregada como uma soma de todos os valores na coluna SalesAmount. Essa soma de todos os valores na coluna% das vendas será sempre 100%.

Dica: Certifique-se de ler contexto em fórmulas DAX. Ele fornece uma boa compreensão do contexto e contexto do nível da linha, que é o que estamos descrevendo aqui.

Podemos excluir nossa porcentagem da coluna calculada pela venda porque ela não vai nos ajudar. Em vez disso, vamos criar uma medida que calcule corretamente nossa porcentagem do total de vendas, independentemente de quaisquer filtros ou segmentações de informações aplicadas.

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

Dica: Criar medidas explícitas como totais SalesAmount e total COGS não só são úteis em uma tabela dinâmica ou relatório, mas elas também são úteis como argumentos em outras medidas quando você precisa do resultado como um argumento. Isso torna suas fórmulas mais eficientes e mais fáceis de ler. Esta é uma boa prática de modelagem de dados.

Criamos uma nova medida com a seguinte fórmula:

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

Esta fórmula diz: divida o resultado do total SalesAmount pela soma total de SalesAmount sem nenhum filtro de coluna ou linha que não seja o definido na tabela dinâmica.

Dica: Lembre-se de ler sobre as funções calcular e de seleção selecionada na referência Dax.

Agora, se adicionarmos nossos novos % do total de vendas à tabela dinâmica, obteremos:

Resultado correto de Soma das % de vendas em Tabela Dinâmica

Isso tem uma aparência melhor. Agora nosso % do total de vendas para cada categoria de produto é calculada como uma porcentagem do total de vendas para o ano de 2007. Se selecionarmos um ano diferente, ou mais de um ano na segmentação de CalendarYear, obteremos novas porcentagens para nossas categorias de produto, mas nosso total geral ainda é 100%. Também podemos adicionar outras segmentações e filtros. Nossa porcentagem 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 qualquer filtro ou segmentação de informações que são aplicadas. Esse é o poder das medidas.

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

Usar colunas calculadas

  • Se você quiser que seus novos dados apareçam em linhas, colunas ou filtros em uma tabela dinâmica, ou em um eixo, uma legenda ou, ao lado de uma visualização do Power View, você deve usar uma coluna calculada. Assim como as colunas regulares de dados, as 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 das datas na coluna Data. Por exemplo, = mês (' data ' [data]).

  • Se você quiser adicionar um valor de texto para cada linha a uma tabela, use uma coluna calculada. Os campos com valores de texto nunca podem ser agregados em valores. Por exemplo, = FORMAT (' Date ' [date], "MMMM") nos dá o nome do mês para cada data na coluna data na tabela Date.

Usar medidas

  • Se o resultado do cálculo será sempre dependente 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 sobre o ano ou variação, use um campo calculado.

  • Se você quiser manter o tamanho da sua pasta de trabalho a um mínimo e maximizar seu desempenho, crie quantos cálculos forem possíveis. 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, não há nada de errado com a criação de colunas calculadas como fizemos com nossa coluna de lucros e, em seguida, agregando-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. Ao compreender esses dois recursos extremamente poderosos do Power pivot crescerem, você desejará criar o modelo de dados mais eficiente e preciso que você pode usar. Espero que o conteúdo que você aprendeu aqui ajude. Há alguns recursos muito incríveis que podem ajudá-lo a você também. Aqui estão apenas alguns: contexto em fórmulas DAX, agregações no Power pivote na central de recursos de Dax. E, embora seja um pouco mais avançado e direcionado para profissionais de contabilidade e Finanças, a amostra de modelagem e análise de dados de lucros e perdas com o Microsoft Power pivot no Excel é carregada com ótimos exemplos de modelagem de dados e fórmulas.

Precisa de mais ajuda?

Expanda suas habilidades no Office
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

×