Adicionando colunas calculadas ao Pivô do Excel sem detalhes

Question

Existe uma maneira de adicionar um campo calculado que se aplicaria apenas à coluna total, sem adicionar detalhes desnecessários para cada item da coluna?

Considere o seguinte exemplo.

Os dados são os seguintes:

Year    Product     Units   Price   Revenue
2013    Porduct A   100     1.5     150
2013    Porduct B   150     1.6     225
2013    Porduct C   200     1.7     300
2013    Porduct D   250     1.8     375
2013    Porduct E   300     1.9     450
2014    Porduct A   150     1.5     225
2014    Porduct B   200     1.6     300
2014    Porduct C   250     1.7     375
2014    Porduct D   300     1.8     450
2014    Porduct E   350     1.9     525

O pivô resultante é o seguinte:

Agora,digamos,desejoadicionarumdescontode10%paracadaproduto,noentanto,quandoadicionoumcampocalculado,recebodetalhesdedescontodesnecessáriosparacadaanoseparadamente:

O que eu realmente quero é me livrar dos detalhes ano a ano e deixar o cálculo apenas para o total:

Mas eu gostaria de conseguir isso sem esconder as colunas, mas simplesmente adicionando um campo, como normalmente faria sem pivô. A razão por trás disso é que pode haver muitos cálculos conseqüentes que eu gostaria de realizar sem fazer o pivô crescer horizontalmente (especialmente quando faço uma análise mês a mês, ele amplia a tabela resultante por 12 colunas toda vez que um campo calculado é adicionado.

    

Answer 1

Tabelas Dinâmicas padrão não permitem isso.

Primeiro, vou descrever como você pode contornar isso usando Tabelas Dinâmicas comuns, depois mostrarei como você pode conseguir isso usando o suplemento do PowerPivot.

Opção 1: duas tabelas dinâmicas padrão

Eu lidaria com isso simplesmente sentando duas mesas ao lado uma da outra, uma com anos como marcador de coluna, e uma sem (essa segunda mesa fica uma linha abaixo para que elas se alinhem).

Você precisa garantir que as colunas de rótulos de linha sejam classificadas e filtradas de forma idêntica. Então você pode apenas ocultar a coluna de marcadores na segunda tabela.

(Nestacapturadetelaeuadicioneimanualmenteosrótulos"Total" na linha 3)

Opção 2: usando o PowerPivot

Usando o PowerPivot e o DAX, você pode criar todas essas colunas como medidas individuais para controlar os filtros aplicados com bastante precisão.

Primeiro, verifique se você tem o PowerPivot instalado - o Excel não vem com ele em algumas edições do Excel 2013. Não vou percorrer o processo de instalação, mas uma pesquisa rápida deve ajudar se você estiver preso.

Selecione seu intervalo de dados e clique em Create Linked Table na barra de ferramentas do PowerPivot.

AjanelaverdedoPowerPivotdeveaparecer.Porpadrão,suatabelaseráchamadasimplesmentedeTabela1-vamosrenomearProductSalesparatorná-lamaissignificativa.

Clique no botão PivotTable no meio da faixa verde e coloque-o onde quiser. Agora precisamos criar algumas medidas. O PowerPivot usa um tipo de código chamado DAX (Data Analysis eXpressions), que é muito semelhante às fórmulas normais do Excel.

De volta ao Excel, comece adicionando Produto ao Row Labels de sua Tabela Dinâmica. Agora clique no botão New Measure na guia da faixa de opções do PowerPivot.

Na janela resultante, defina Measure name (all PivotTables) para Unidades Total e, na caixa de texto Description free, insira:

=sum(ProductSales[Units])

Crieoutramedida,chamadaUnidades2013.Digiteestecódigo:

=CALCULATE([UnitsTotal],ProductSales[Year]=2013)

A função CALCULATE() em DAX permite aplicar (vários) filtros a uma expressão agregada. Por isso, aqui estamos filtrando a medida Unidades total que acabamos de criar, até o ano relevante . Repita para Unidades 2014 e, em seguida, faça o mesmo novamente para Total de receita , Receita 2013 & Receita 2014 .

Agora você pode pedir e formatar suas colunas como quiser. Para imitar suas capturas de tela acima, defini manualmente o 2013 & Colunas de 2014 para cinza.