Percent of lowest level by highest level of matrix hierarchy

[josipinho]

I am trying to get percentages in matrix, so that my percentage is calculated as percentage of higher level in my hierarchy. To make it more clear this is how my matrix looks fully extended:

I blanked out some names for privacy reasons. So I have hotels, marketing groups, marketing segments. I want to be able to drill up and down, and I would like to have one collumn that shows % of marketing segments, and one collumn that is % of hotel total.

As you can see right now using quick measures I can only get % of column total or % of grand total, but It is not what I have to get.

By searching on this forum I found an awesome post that gave me % group, but it is still one step short of what I need.

I would like a collumn where I can get the % that is calculated by dividing with HOTEL total but I just cant get it to work.

So ideally how it would look is something like this

For example, my HOTEL 1 had 6928 people, and then I show those numbers by marketing group, and marketing segment. 

What I need to have is a collumn that shows the percentage of my marketing segment / hotel total

so it would look something like this when drilled down to the lowest level

In a nutshell, I need to divide individually the lowest level in my "hierarchy" with the sum of the highest level in the hierarchy.

Hopefully I managed to explain my question good enough...

[josipinho · Accepted solution]

This answer from one of the other thread helped me:

Create % for the smallest level (items)

% level 3 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Item]) ) )
The Second level

% level 2 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Category]) ) )

And the first level (markets)
% level 1 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Market]) ) )

As my observation, when showng 1 level in matrix, the parent will be 100% or 1, so I will use IF condition to replace this value to its percentage in the previous level
Create measure for Level 2:

Level 2 = if([% level 3]=1 , [% level 2],[% level 3])

Finally, create measure for level 1 with name % group:
% group = if([Level 2]= 1,[% level 1] ,[Level 2])

[CheenuSing]

Hi @josipinho

Can you share some sample data in excel format masking the hotel names to work out a solution. Share the link of the excel data file on onedrive or google drive here.

Also please share the different columns in the final output expected and as shown in the message posted.

Cheers

CheenuSing