Percentage calculation in pivot for hierarchy display

[#1]

Hello all,

I have a pivot table that display the hierarchy I’ve been created (thanks to the people here that help me to achieve that).

What I’m trying to do is to calculate the % of users that continue to the next step base on the amount of users that participate in the previous step

The table below illustrate what I have and what is my expected column for % calculatio

So for every branch that I will open I will have the % calculation according to the previous step

Gender	Step1	Step2	Step3	Step4	Step5	users	expected
unknown	Responda	Mulher	Nenhuma	- Pantene	Nao	61	61/194
					Sim	91	91/194
					-	194
					Total	194
				- TRES-emme		83	83/833
				-Nenhuma		334	334/833
				-WellaProSeries		50	50/833
				-		833
				Total		833
			Total			833
		Total				833
	Total					833

I guess that set analysis will be my solution here but I could not understand how to do it.

Any advice its highly appreciated.

Tomer

[whiteline · Accepted solution]

You can do somthing like that in different expressions:

=Count(users)/Count(total<Gender, Step1, Step2, Step3, Step4>users)

=Count(users)/Count(total<Gender, Step1, Step2, Step3>users)

Then you can combine them into one with "ifs" and dimensionality() function.

[#3]

Thanks that was very helpful.

I did the following:

Count the users in one column

Divide it according to the dimensionality() level

Any idea how to remove the null from the pivot display? (the bold line)

Gender	Q1	Q2	Q3	Q4	users	full Sequence	from Starters
unknown	Responda	Homem	Nenhuma	- Pantene	147	24.26%	10.22%
				- TRES-emme	30	4.95%	2.08%
				-Nenhuma	259	42.74%	18.00%
				-WellaProSeries	52	8.58%	3.61%
				-	606	100.00%	42.11%
				Total	606	100.00%	42.11%