Display Hierarchy data while showing cross direction ratio

[samratpbi]

Recently, I came across a requirement where I had to display hierarchy data. Lower levels of data should be shown as a ratio of all the segments available rather than a ratio in its own group. Also, some multiplication factor needs to be applied. In addition, hierarchy needs to be summed up from lower levels instead of grouping and rolling up. 

Let's start with the data first (attached). I created a dummy data. But let me explain it.

Here we have Region wise Sales data. Level 1 shows different strategies (DirectSales, RetailSales, Phone). Level 2 shows their corresponding channels and sub channels.

Below is the multiplication factor which is applied for different sales channel, but same for all region.

First thing is to ingest the data and doing necessary transformation using Power Query.

For Sales Table, Region column, there are blanks which needs to be filled up based on above Regions. Use Fill Down from Power Query:

Then I have some blanks in Level 3. Idea is to have those filled up with the same value as of Level 2. Hence create a Column to replicate Level 2 Value when Level 3 is blank. Then remove existing Level 3 column and rename the new column as level 3.

I need to add few more transformation, but let me explain why I would need that and then I will add accordingly. I also have a dimension table for Levels.

Now my requirement is to show each level's ratio in its category across all the regions rather than its own level and then multiply it with the multiplication factor. Below are the measures I created:

Total Sales = SUM(Sales[Value])  -- Returning Total Sales

Total Sales All Region =  -- Returning Sales across Region

CALCULATE(

    [Total Sales],

    REMOVEFILTERS(Sales[Region])

)

Total Sales Ratio = DIVIDE([Total Sales],[Total Sales All Region],0) -- Ratio of a channel sales in between all region

Total Factor = SUM(Factor[Factor]) --Total Factor

Total Sales Factor = [Total Sales Ratio] * [Total Factor]  -- Sales multiplied by Factors

After creating all these, below I put into a matrix visual.

I have kept all the intermediate measures in the table to show values.

Initially it looked fine however if you look into the last column which is the main KPI need to be displayed, its value are not summed to 100% in higher level. That is because in each level of hierarchy calculation happening considering that level.

For example for Region R1 -> Level 1 DirectSales -> Level 2 Digital if we consider Total Sales Ratio, that is 1228/6051 = 20.29% whereas for each individual Level 3, those are different. simply because a/x + b/y + c/z is not equal to (a+b+c) / (x+y+z) and later is the case how calculation happening for Level 2. What we needed in this case is to sum up lower level data in higher level. and also while applying multiplication factor.

The way we can achieve that is using row-wise calculation and then sum up. Hence to achieve that, need to make further transformation in Power Query first.

First create a duplicate of the Sales fact table and remove other columns than Level 1, Level 2, Level 3 and value. And then group by all Level columns and sum up.

Then merge that new table with the Sales table.

Then create a measure which would be replacement of earlier Total Sales Factor measure. We need to do row wise calculation and then sum up, hence use SUMX.

Total Sales Factor New = SUMX(Sales, DIVIDE(Sales[Value],Sales[ValueRegion]) * [Total Factor])

Below is the outcome:

Now if you look into the last measure, each individual items from Level 3 are getting summed up to Level 2 and then Level 1, which was the objective.

Used excel and the pbix file are attached.

There might be other ways to achieve the same, however this one I fould easy to set up and achieve the goal.

Hierarchy.pbix