Issue with total column when expression modified as per dimension

[#1]

Hello,

I am facing an issue with total column. I have a simple pivot table like this:

Challenge here is, that for current and previous quarters I must multiply result *4, which is very simple, I changed expression to:

=
IF(TextBetween([Qtr],'Q','-') <= Ceil(Month(Today(1)) / 3),
sum(Value)*4,
sum(Value)
)

This shows data fine on changes pivot:

BUT

Total column is wrong, it is showing same value as previously. This is probably connected to my expression for calculationg current and previous quarters (as in total Qtr column does not exist)

I tried working with SecondaryDimensionality, but had no luck.

How can I force Total column to the right to work...

Edit: Attaching sample file.

BR,

Kuba

[veidlburkhard · Accepted solution]

Hi saperos_,

you have to use Aggr function to solve this problem.

Please change your expression to

=Sum(Aggr(

IF(TextBetween([Qtr],'Q','-') <= Ceil(Month(Today(1)) / 3),

sum(Value)*4,

sum(Value)

), Division, Location, Qtr))

Enjoy

Burkhard

[#3]

Thank you so much Burkhard! Another solution around AGGR() funditon.

Can you be that kind and explain me how exactly this works? It seems I am unable to understnad when and how to use Aggr() function...

BR,

Kuba

[veidlburkhard]

Hi Kuba,

There is some explanation how to use the Aggr function in the help file. Additionally you should have a look into the attached pdf file.

Hope this helps

Burkhard