Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate Percent of whole based on Row Heirarchy in Matrix

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.

 

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

DMS4.jpgDMS5.jpg

 

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

This image shows the Mix RN's measure calculating the percentages accuratelyThis image shows the Mix RN's measure calculating the percentages accurately

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.

DMS2.jpg

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.

DMS3.jpg

Row Heirarchy in Matrix

DMS6.jpg

2 REPLIES 2
amitchandak
Super User
Super User

Rooms are only by room type. All others are applicable to occupied rooms. So that will not work out unless there is a formula distribute it on other dimension like market segment.

 

 

Anonymous
Not applicable

What would this mean for the DAX of "Mix Rn's"?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.