Return the percent total of the parent row

[pfarias]

I'm trying to do, dynamically, what is the percentage share of the value of the parent row.

In excel, using a pivot table, I can return by clicking on SHOW VALUES AS >> % Total of the parent row.

How can I do this using DAX?

[Thejeswar]

Hi @pfarias ,

Just remove the filter on the table in your denominator of the measure for getting the %. That will do.

Sharing the sample measure that I created. Change it as per your needs

%ofTotal = DIVIDE(SUM('Table'[Amount]), CALCULATE(SUM('Table'[Amount]), REMOVEFILTERS('Table')))

Below is the ss for reference

If this helps, mark it as a solution. You can also appreciate with a kudo!!

[pfarias]

If there is only one level, this way it is solved. Now when there are multiple levels? How to do it dynamically?

[Thejeswar]

HI @pfarias ,

I can see this working for both 2 level matrix and 3 level matrix automatically without any changes to the logic.

To help easily solve this, kindly share some sample data and the output that you are expecting to see.

[FilipAi]

The method you suggest is % of Grand total. The question (which I am also struggling with) is how to show % of parent row. E.g. AA = 156, AC = 67. Parent is A = 223. AA % of parent is 156/223 = 70 %.

Nowadays, this can be done with Visual calculations in PBI, but I need to use measures through field parameters, so the measure needs to exist in the model. And the row level depth is dynamic.

Any help appreciated!

[Thejeswar]

Hi @FilipAi ,

You can achieve this, But a slight modification is required in the DAX we use.

I have taken a different example since I don't anymore have the dataset I was using earlier

The Below is how the DAX will look.

%ofTotal = IF(
		ISINSCOPE('Table'[Month]),
		DIVIDE(
			SUM('Table'[Item Quantity]),
			CALCULATE(
				SUM('Table'[Item Quantity]),
				REMOVEFILTERS('Table'),
				VALUES('Table'[Year])
			)
		),
		DIVIDE(
			SUM('Table'[Item Quantity]),
			CALCULATE(
				SUM('Table'[Item Quantity]),
				REMOVEFILTERS('Table')
			)
		)
	)

The Screenshot is given below

If you see in the above screenshot, 2023 makes for 47% of total, then considering 2023 as the parent, the Months are shown as % of 2023 with 100% as the total value

Regards,

[FilipAi]

This works as a simplified solution, if the row fields are static. If the month would be placed above year (e.g. for MoM comparisons), the measure would be incorrect I believe.

The visual calculation method works either way, infortunately not if you want to select measures through field parameters (afaik).