Quando utilizar as Colunas Calculadas e os Campos Calculados

Ao aprender pela primeira vez como utilizar o Power Pivot, a maioria dos utilizadores deteta que o poder real está a agregar ou calcular um resultado de alguma forma. Se os seus dados têm uma coluna com valores numéricos, pode agregá-los facilmente ao selecioná-los numa Tabela Dinâmica ou numa Lista Power View Campos. Por natureza, uma vez que é numérico, é somado automaticamente, calculada a 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 colunascalculadas.

Vamos ver primeiro um exemplo em que utilizamos uma coluna calculada para adicionar um novo valor de texto para cada linha de uma 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 Preço do Preço, etc. Temos outra tabela relacionada denominada Categoria de Produtos 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. Na nossa tabela Produto, podemos criar uma coluna calculada denominada Categoria de Produtos da seguinte forma:

A nossa nova fórmula Product Category utiliza a função RELATED DAX para obter valores da coluna ProductCategoryName na tabela relacionada Product Category e, em seguida, introduz esses valores para cada produto (cada linha) na tabela Product.

Este é um excelente exemplo de como podemos utilizar uma coluna calculada para adicionar um valor fixo para cada linha que podemos utilizar posteriormente na área LINHAS, COLUNAS ou FILTROS da tabela dinâmica ou num relatório Power View.

Vamos criar outro exemplo em que queremos calcular uma margem de lucro para as 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ções e existe uma relação entre a tabela Vendas e a tabela Categoria de Produto. Na tabela Vendas, temos uma coluna que tem os montantes de vendas e outra coluna que tem os custos.

Podemos criar uma coluna calculada que calcule um valor de lucro para cada linha ao subtrair valores na coluna COGS a valores na coluna SalesAmount, da mesma forma:

Agora, podemos criar uma Tabela Dinâmica e arrastar o campo Categoria de 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 lucro para cada uma das diferentes categorias de produtos. O nosso resultado tem o mesmo aseque isto:

Neste caso, o lucro só faz sentido como um campo em VALORES. Se colocarmos Lucro na área COLUNAS, a nossa Tabela Dinâmica teria o mesmo aspas:

O nosso campo Lucro não fornece quaisquer informações úteis quando é colocado nas áreas DE COLUNAS, LINHAS ou FILTROS. Apenas faz sentido como um valor agregado na área VALORES.

O que fizemos é criar uma coluna denominada Lucro que calcula uma margem de lucro para cada linha da tabela Vendas. Depois adicionámos Lucro à área valores da nossa Tabela Dinâmica, criando automaticamente uma medida implícita, onde um resultado é calculado para cada uma das categorias de produtos. Se estiver a pensar que calculámos realmente o lucro para as nossas categorias de produtos duas vezes, tem a razão. 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 é que calculamos o nosso lucro sem criar uma coluna calculada Lucro?

Na verdade, o lucro seria melhor calculado como uma medida explícita.

Por agora, vamos deixar a nossa coluna Calculada lucro na tabela Vendas e Categoria de Produtos em COLUNAS e Lucro em VALORES da nossa Tabela Dinâmica, para comparar os nossos resultados.

Na área de cálculo da tabela Vendas, iremos criar uma medida chamada Lucro Total(para evitar conflitos de no nome). No fim, irá produzir os mesmos resultados que anteriormente, mas sem uma coluna calculada Lucro.

Em primeiro lugar, na tabela Vendas, selecionamos a coluna SalesAmount e, em seguida, clicamos em Soma Automática para criar uma medida explícita Soma do 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 dar o nome a estas TOTAL SalesAmount e Total de COGS para facilitar a sua identificação.

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

Lucro Total:=[ Total do SalesAmount] - [Total de COGs]

Após alterar o formato da nossa nova medida Lucro Total para a moeda, podemos adicioná-lo à 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 de momento e apenas para os campos que selecionamos para a nossa Tabela Dinâmica. Afinal não precisamos da coluna calculada Profit.

Porque é que esta última parte é importante? As colunas calculadas adicionam dados ao modelo de dados e os dados ocupam memória. Se atualizamos 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 tirar recursos como este porque queremos mesmo calcular o nosso lucro quando selecionamos os campos que pretendemos Lucro na Tabela Dinâmica, como categorias de produtos, região ou datas.

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

Neste exemplo, queremos calcular os montantes de vendas como uma percentagem das vendas totais. Criamos uma coluna calculada denominada % de Vendas na nossa tabela Vendas, da seguinte forma:

A nossa fórmula indica: Para cada linha na tabela Vendas, divida o montante na coluna SalesAmount pelo total da 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 a colocar em VALORES, obteremos um total da soma de % de Vendas para cada uma das nossas categorias de produtos.

OK. Até agora, parece-me tudo bem. Mas, vamos adicionar uma fatia. Adicionamos Ano de Calendário e, em seguida, selecionamos um ano. Neste caso, selecionamos 2007. É o que acontece.

À primeira vista, ainda poderá estar correto. No entanto, as nossas percentagens deverão totalizar 100%, porque queremos saber a percentagem total de vendas para cada uma das nossas categorias de produtos para 2007. O que correu mal?

A nossa coluna % de Vendas calculou uma percentagem para cada linha que seja o valor na coluna SalesAmount dividida pelo total da soma de todos os valores na coluna SalesAmount. Os valores numa coluna calculada são fixos. São um resultado imutável para cada linha da tabela. Quando adicionámos a % de Vendas à nossa Tabela Dinâmica, esta era agregada como uma soma de todos os valores na coluna SalesAmount. Essa soma de todos os valores na coluna % de Vendas será sempre 100%.

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 dos filtros ou as filtragems aplicadas.

Lembra-se da medida TotalSalesAmount que criámos anteriormente, a que soma simplesmente a coluna SalesAmount? Utilizamos-a como um argumento na nossa medida Lucro Total e vamos usá-la novamente como um argumento no nosso novo campo calculado.

Criamos uma nova medida com a seguinte fórmula:

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

Esta fórmula indica: Divida o resultado do Total SalesAmount pelo total da soma do SalesAmount sem quaisquer filtros de coluna ou linha além dos definidos na Tabela Dinâmica.

Agora, se adicionarmos a nossa nova % de Vendas Totais à Tabela Dinâmica, obtemos:

Assim fica melhor. Agora a nossa % do Total de Vendas para cada categoria de produto é calculada como uma percentagem das vendas totais do ano 2007. Se selecionarmos um ano diferente ou mais do que um ano na slicer CalendarYear, obteremos novas percentagens para as nossas categorias de produtos, mas o nosso total global ainda é 100%. Também podemos adicionar outras fatias e filtros. A nossa medida % do Total de Vendas produzirá sempre uma percentagem das vendas totais, independentemente das vendas ou filtros aplicados. Com as medidas, o resultado é sempre calculado de acordo com o contexto determinado pelos campos nas COLUNAS e LINHAS e por quaisquer filtros ou filtragems de dados aplicados. Este é o poder das medidas.

Eis algumas diretrizes para o ajudar a decidir se uma coluna calculada ou uma medida é adequada para uma determinada necessidade de cálculo:

Utilizar colunas calculadas

• Se pretender que os novos dados apareçam em LINHAS, COLUNAS ou FILTROS numa Tabela Dinâmica ou num EIXO, LEGENDA ou MTIL BY numa visualização de Power View, tem de utilizar uma coluna calculada. Tal como nas colunas normais de dados, as colunas calculadas podem ser utilizadas como um campo em qualquer área e, se são 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 calcule apenas o número do mês 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('Data'[Data],"mmmm") fornece-nos o nome do mês de 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 fazer cálculos mais complexos, como calcular uma contagem com base num filtro de alguma ordenação ou calcular um ano ao longo do ano, ou variância, utilize um campo calculado.

• Se pretender manter o tamanho do livro para um mínimo e maximizar o seu desempenho, crie o maior número de cálculos possível. Em muitos casos, todos os 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 com a criação de 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 saber mais e criar os seus próprios cálculos. À medida que a compreensão destas duas funcionalidades extremamente avançadas do Power Pivot cresce, irá querer criar o modelo de dados mais eficiente e preciso que conseguir. Esperamos que o que aprendeu aqui ajude. Existem outros recursos realmente fantásticos que também o podem ajudar. Eis apenas alguns: Contexto em Fórmulas DAX, Agregações no Power Pivote Centro de Recursos DAX. Além disso, embora seja um pouco mais avançado e direcionado para profissionais de contabilidade e finanças, a amostra Modelação e Análise de Dados de Lucros e Perdas com o Microsoft Power Pivot em Excel é carregada com excelentes exemplos de modelação de dados e fórmulas.