2 Replies Latest reply: Mar 30, 2018 5:52 AM by Petter Skjolden

How to make dynamic percentage calculation for each group on pivot

kaan erisen Mar 30, 2018 4:18 AM

Hi all,

I want to show percentage %ofParentTotal for each group on pivot table. I can make it by checking dimensionality of cell and calculate the %ofParentTotal for each group.

sum(VALUE)/

Pick(Dimensionality()+1,

sum(total VALUE),

sum(total <DIM1> VALUE),

sum(total <DIM1,DIM2> VALUE),

)

but I need to calculate it without checking dimensionality. Actually, I don't want to edit the expression when any dimension is added. It is not effective

Is there any way to achieve this?

Thanks,

ratio for each group.qvf 192.0 K

Re: How to make dynamic percentage calculation for each group on pivot

Petter Skjolden Mar 30, 2018 5:36 AM (in response to kaan erisen)

I guess this is closer to what you are looking for?
Like Show 0 Likes (0)

Actions
Re: How to make dynamic percentage calculation for each group on pivot

Petter Skjolden Mar 30, 2018 5:52 AM (in response to kaan erisen)

Furthermore you can make it dynamic as to which two first dimensions you put in the pivot by using the GetObjectField() function. It is a rather strange name for the function since it retrieves the name of the dimension you select:

Sum(VALUE)/SUM(TOTAL <$(=GetObjectField(0)),$(=GetObjectField(1))> VALUE)

This expression will adjust to whichever dimensions you put in the pivot.
Like Show 0 Likes (0)

Actions

Go to original post