Pivot table is only showing the values when using filters

[NathanS2022]

Hi all, 

I need help with the following issue. I have this Pivot table that must be based on an existing Excel spreadsheet.

Consider:

N2_ORCAMENTO = 'Comissões S/ Vendas' and 'CPV/CMV'

N1_ORCAMENTO = 'MARCA 1', 'MARCA 2' and 'MARCA 3'

Valores = The percent values

PERIODO = Months

Here's the spreadsheet it's based on 

As you can see in the spreadsheet, the values under the N2 ('CPV/CMV' and 'Comissões S/ Vendas') are not the sum of Marca 1 to 3. And that's how it's supposed to be. I got the correct values but they only show by the side of Marca 1 to 3 when I use filters for N2.

The calc for this percentage value is the sum of VALOR divided by the TOTAL SUM of VALOR where N2 = 'VENDA BRUTA'. Something like Sum(VALOR)/Sum(TOTAL {<N2_ORCAMENTO={'VENDA BRUTA'} VALOR)

Here's my expression:

Sum(VALOR)
/
Sum(TOTAL <N1_ORCAMENTO,PERIODO> Aggr(Sum(TOTAL <N1_ORCAMENTO,PERIODO> {<N2_ORCAMENTO={'VENDA BRUTA'}>} VALOR), N1_ORCAMENTO,PERIODO))

How can I make it so the value is always shown? Thanks in advance.

[Aurelien_Martinez]

Hi,

Try to add N2_ORCAMENTO in the Aggr, like

Sum(VALOR)
/
Sum(TOTAL <N1_ORCAMENTO,PERIODO> Aggr(Sum(TOTAL <N1_ORCAMENTO,PERIODO> {<N2_ORCAMENTO={'VENDA BRUTA'}>} VALOR), N1_ORCAMENTO,PERIODO, N2_ORCAMENTO ))

Aurélien

[NathanS2022]

Hello. Thanks for your answer.

Unfortunately I tried that before and it didn't work. For now, I rewrote the expression in a simpler way to try and understand better using only the value that will be used on the division later. Sadly with no success yet. I'm trying to match the value correctly by doing:

Sum(TOTAL <PERIODO,N1_ORCAMENTO> Aggr(Sum({<N2_ORCAMENTO={'VENDA BRUTA'}>} VALOR), N1_ORCAMENTO, PERIODO))

Just to be clearer. The values I need to use on the expression to show on all above cells belong to Marca 1 to 3 under the 'VENDA BRUTA'. So I would have the Sum(VALOR) divided by Sum(VALOR) of 'VENDA BRUTA', and so on for different lines where Marca 1 to 3 are present.

Here's what happens when I collapse the line of 'VENDA BRUTA':

[NathanS2022 · Accepted solution]

I finally did it. I had to add another AGGR() with all 3 fields and then another SUM()

Like this: 

Sum(Aggr(Sum(TOTAL <PERIODO,N1_ORCAMENTO> Aggr(Sum({<N2_ORCAMENTO={'VENDA BRUTA'}>} VALOR_ORCAMENTO), N1_ORCAMENTO,PERIODO)),N2_ORCAMENTO,N1_ORCAMENTO,PERIODO))