Calculate Percent of whole in a column for each row level

[Anonymous]

I am building a matrix where the "Mix RN's" measure is used to calculate the percentage of rooms rented against the number of rooms selected according to an individual category. The Categories are the Row levels in the matrix visual "Type, Universal Description, Brand, and PMS Description".

The problem is that the measure only works on the lowest level of the matrix, PMS descritpion to Brand. I am trying to make it so that the measure works on all levels of the matrix. Because right now all Brands are coming in as 100% of themselves as opposed to as a percentage of Universal Description. and so on for Universal Description to Type.

This is the row heirarchy in the Matrix visual

These are my current measures for Mix RN's and Mix RN's Last Year(LY)

The image below shows the Mix RN's measure calculating the percentage accurately for PMS Description to Brand

This image shows when the Mix RN's measure stops calculating the percenatge when the matrix rows are collapsed. It won't calculate for the Brand as a percentage of Universal Description. i.e aLoft as a percent of Retail.

This image shows when the Mix RN's measure stops calculating the percenatge when the matrix rows are collapsed. It won't calculate for the Universal Description as a percentage of Type. i.e Retail as a percent of Transient.

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please refer to the video.

ALL() VS ALLSELECTED() 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.