Calculating Measures based on Hierachy level

[marcelmuench]

Hi Power BI Community,

we want to create a dashboard to track our digital performance. Therefore we have defined some KPIs and a Hierarchy for them and also ratios to weight the KPIs differently.

Here as an example:

Level 1 Facebook

Level 2a Facebook Likes-Rate (Weight 60%)

Level 2b Facebook Comment-Rate (Weight 40%)

So the Like-Rate counts 60% and the Comment-Rate 40% and aggregated it's the Facebook Score (very simplified). We want to slice this by time, I have the KPIs on a daily base but they also want to look at it weekly or monthly.

The problem I'm facing is, that the rates are relative metrics, so behind them there are two metrics which have to be divided first.

Facebook-Likes-Rate = Count of Likes / Count of Posts (e.g. 50 Likes / 100 Posts = 0,5)

Facebook Comment-Rate = Count of Comments / Count of Posts (e.g. 10 Likes  / 100 Posts = 0,1)

On the lower level it's easy to calculate, because there is no weight. So on this level we can see 0,5 for the Like-Rate and 0,1 for the Comment-Rate.

Now I've tried to calculate the Level above and I have expected the following result:

Facebook = Like-Rate * 60 % + Comment-Rate * 40 % = (0,5* 60 %) + (0,1 * 40 %) -> 0,34 as a Score value (we have benchmarks to compare this value).

But I'm not able to do this so far. I tried some solutions but the problem is, that the deepest level need to be calculated dynamically via a calculated measure. If I take this measure as a base for the above level to calculate it's dividing still through the base values:

(10 Likes * 60 % + 10 Comments * 40 %) / (100 Posts * 60 %+ 100 Posts * 40 %) =(0,6 + 0,4) / (60 + 40) = 0,01

Not quite sure if this was understandable. It's a very tricky thing to me.

Here is a little snapshot with some fake data:

The numbers of the column KPI Measure should be multiplied by the numbers of the column KPI Level 3 Weight. And then the sum of those 3 values should be there where we now can see the 4,09. 

Any solution for this?

Thanks a lot!

Best, 

Marcel

[v-shex-msft]

Hi @marcelmuench,

According to your description, I think you can take a look a the following blog to know more about the hierarchy levels and how to use if statements and filters to check the current row content level.

Clever Hierarchy Handling in DAX 

Regards,

Xiaxoin Sheng

[amitchandak]

@marcelmuench , Not very clear to me. But isinscope should help

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/