Quando aprendem a utilizar o Power Pivot pela primeira vez, a maioria dos utilizadores 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, pode agregá-los facilmente ao selecioná-los numa Tabela Dinâmica ou numa Lista de Campos do Power View. Por natureza, por ser numérico, será automaticamente somado, calculado a média, contado ou qualquer que seja o tipo de agregação que selecionar. Isto é 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, que podem quase sempre ser ultrapassados com medidasexplícitas e colunas calculadas.
Primeiro, vejamos um exemplo em que utilizamos uma coluna calculada para adicionar um novo valor de texto para cada linha numa tabela chamada Produto. Cada linha da 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 Product Category 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 Produtos, podemos criar uma coluna calculada com o nome Categoria de Produtos da seguinte forma:
A nossa nova fórmula Product Category utiliza a função DAX RELATED para obter valores a partir da coluna ProductCategoryName na tabela da Categoria de Produtos relacionada e, em seguida, introduz esses valores para cada produto (cada linha) na tabela Product (uma linha).
Este é um excelente exemplo de como podemos utilizar uma coluna calculada para adicionar um valor fixo para cada linha que pode ser utilizado mais tarde na área LINHAS, COLUNAS ou FILTROS da Tabela Dinâmica ou num relatório de Power View.
Vamos criar outro exemplo onde queremos calcular uma margem de lucro para as nossas categorias de produtos. Este é um cenário comum, mesmo em muitos tutoriais. Temos uma tabela Vendas no nosso modelo de dados com dados de transação e existe uma relação entre a tabela Vendas e a tabela Categoria de Produtos. Na tabela Vendas, temos uma coluna que tem os montantes das vendas e outra coluna que tem os 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, desta forma:
Agora, podemos criar uma Tabela Dinâmica e arrastar o campo Categoria de Produtos 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 este aspeto:
Neste caso, Lucro apenas faz sentido como um campo em VALORES. Se colocássemos Lucro na área COLUNAS, a nossa Tabela Dinâmica teria este aspeto:
Nosso campo Lucro não fornece nenhuma informação útil quando é colocado nas áreas COLUNAS, LINHAS ou FILTROS. 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 da tabela Vendas. Em seguida, adicionámos Lucro à área de VALORES da nossa Tabela Dinâmica, criando automaticamente uma medida implícita, onde é calculado um resultado 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á certo. Primeiro, calculámos um lucro para cada linha da tabela Vendas e, em seguida, adicionámos o lucro à área VALORES, onde foi agregado para cada uma das categorias de produtos. Se também pensa que não precisávamos de criar a coluna calculada do lucro, também está correto. Mas, como então calculamos nosso lucro sem criar uma coluna calculada de Lucro?
O lucro, na verdade, seria melhor calculado como uma medida explícita.
Por agora, vamos deixar a nossa coluna calculada de Lucro na tabela Vendas e Categoria de Produto em COLUNAS e Lucro em VALORES da nossa Tabela Dinâmica, para comparar os 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, produzirá os mesmos resultados que produzimos anteriormente, mas sem uma coluna calculada de Lucro.
Primeiro, na tabela Sales, selecionamos a coluna SalesAmount e clicamos em AutoSum para criar uma medida explícita da Soma do SalesAmount . Lembre-se, 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. Mudaremos o nome a estes itens de Total SalesAmount e Total COGS para facilitar a sua identificação.
Em seguida, criamos outra medida com esta fórmula:
Total do Lucro:=[Total SalesAmount] - [Total de CPV]
Nota
Também podemos escrever a nossa fórmula como Lucro Total:=SOMA([MontanteVendas]) - SOMA([CPV]), mas ao criar medidas separadas de Total de VendasMontante e Total de CPV, podemos usá-las também na nossa Tabela Dinâmica e como argumentos em todos os tipos de outras fórmulas de medida.
Depois de alterar o formato da nossa nova medida Lucro Total para moeda, podemos adicioná-la à nossa Tabela Dinâmica.
Pode ver que a nossa nova medida Lucro Total devolve os mesmos resultados que criar uma coluna calculada do Lucro e, em seguida, colocá-la em VALORES. A diferença é que a nossa medida de Lucro Total é muito mais eficiente e torna o nosso modelo de dados mais limpo e mais simples porque estamos a calcular no momento e apenas para os campos que selecionamos para a 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 são necessários para recalcular todos os valores na coluna Lucro. Não precisamos de utilizar recursos desta forma porque queremos realmente calcular o nosso lucro quando selecionamos os campos para os quais queremos Lucro na Tabela Dinâmica, como categorias de produtos, 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 montantes de vendas como uma percentagem do total de vendas. Criamos uma coluna calculada com o nome % de Vendas na nossa tabela Vendas, da seguinte forma:
A nossa fórmula indica: Para cada linha na tabela Vendas, divida a quantia na coluna SalesAmount pelo total da SOMA de todas as quantias na coluna SalesAmount.
Se criarmos uma Tabela Dinâmica, adicionarmos Categoria de Produtos a COLUNAS e selecionarmos a nossa nova coluna % de Vendas para a colocar em VALORES, obtemos um total de % de Vendas para cada uma das nossas categorias de produtos.
Está bem. Isso parece bom até agora. Mas, vamos adicionar uma segmentação de dados. Adicionamos Calendar Ano e, em seguida, selecionamos um ano. Neste caso, selecionamos 2007. É isso que temos.
À primeira vista, isso ainda pode parecer correto. Mas, as nossas percentagens devem realmente totalizar 100%, porque queremos saber a percentagem das vendas totais para cada uma das nossas categorias de produtos para 2007. Então, o que correu mal?
A nossa coluna % de Vendas calculou um valor para cada linha que é o valor na coluna SalesAmount dividido 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 % das Vendas à nossa Tabela Dinâmica, esta foi agregada como uma soma de todos os valores na coluna SalesAmount. Essa soma de todos os valores na coluna % de Vendas será sempre 100%.
Sugestão
Certifique-se de que lê Contexto em Fórmulas DAX. Ele fornece uma boa compreensão do contexto de nível de linha e contexto de filtro, que é o que estamos descrevendo aqui.
Podemos eliminar a nossa coluna calculada da % de Vendas porque não nos vai ajudar. Em vez disso, vamos criar uma medida que calcula corretamente a nossa percentagem das vendas totais, independentemente dos filtros ou segmentações de dados aplicados.
Lembra-se da medida do TotalSalesAmount que criámos anteriormente, aquele 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.
Sugestão
A criação de medidas explícitas, como o Total SalesAmount e o Total COGS, não só são úteis numa Tabela Dinâmica ou relatório, como também são úteis como argumentos noutras medidas quando precisa do resultado como argumento. Isto torna as suas fórmulas mais eficientes e fáceis de ler. Esta é uma boa prática de modelação de dados.
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 do Total SalesAmount pelo total da soma do SalesAmount sem filtros de coluna ou linha diferentes dos definidos na Tabela Dinâmica.
Sugestão
Certifique-se de que lê as funções CALCULATE e ALLSELECTED na Referência DAX.
Agora, se adicionarmos a nossa nova % do Total de Vendas à Tabela Dinâmica, obtemos:
Parece melhor. Agora, a nossa % do Total de Vendas para cada categoria de produto é calculada como uma percentagem das vendas totais para o ano de 2007. Se selecionarmos um ano diferente ou mais de um ano na segmentação de dados CalendarYear, obtemos novas percentagens para as nossas categorias de produtos, mas o nosso total geral ainda é de 100%. Também podemos adicionar outras segmentações de dados e filtros. A nossa medida de % do Total de Vendas produzirá sempre uma percentagem das vendas totais, independentemente de quaisquer segmentações de dados ou filtros aplicados. Com as medidas, o resultado é sempre calculado de acordo com o contexto determinado pelos campos em COLUNAS e LINHAS e pelos filtros ou segmentações de dados aplicados. Este é o poder das medidas.
Eis algumas diretrizes para o ajudar quando decidir se uma determinada coluna ou medida é adequada para uma determinada necessidade de cálculo:
Utilizar colunas calculadas
- Se quiser que os novos dados sejam apresentados em LINHAS, COLUNAS ou em FILTROS de uma Tabela Dinâmica, ou num EIXO, LEGENDA ou LADO A LADO numa visualização do Power View, tem de utilizar uma coluna calculada. Tal como as colunas de dados normais, as colunas calculadas podem ser utilizadas como um campo em qualquer área e, se forem 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 pretende 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 a partir das datas na coluna Data. Por exemplo, =MÊS('Data'[Data]).
- Se pretender 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, =FORMATAR('Data'[Data],"mmmm") dá-nos o nome do mês para cada data na coluna Data na tabela Data.
Medidas de utilização
- Se o resultado do seu 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 algum tipo, ou calcular um ano após ano, ou variância, utilize um campo calculado.
- Se pretender reduzir ao mínimo o tamanho do livro e maximizar o desempenho, crie o maior número possível de cálculos como medidas. Em muitos casos, todos os seus cálculos podem ser medidas, reduzindo significativamente o tamanho do livro e acelerando o tempo de atualização.
Lembre-se de que não há nada de errado em criar colunas calculadas tal como fizemos com a nossa coluna Lucro e, em seguida, agregá-la numa Tabela Dinâmica ou num relatório. É, na verdade, uma forma muito boa e fácil de aprender e criar os seus próprios cálculos. À medida que a sua 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. Espero que o que você aprendeu aqui ajude. Existem alguns outros recursos realmente excelentes que podem ajudá-lo também. Eis alguns exemplos: 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 Prejuízo com o Microsoft Power Pivot no Excel está carregado com ótimos exemplos de modelagem de dados e fórmulas.