cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Calculating Measures based on Hierachy level

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:

marcelmuench_1-1602766455520.png

 

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

2 REPLIES 2
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User IV
Super User IV

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

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors