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

Data aggregation issues for different hierarchical levels

Hi, I am having a hard time dealing with different hierarchical levels within a dataset, if anyone could point me to a solution, I would appreciate.

 

My dataset (see table bellow) contains scores for variables aggregated in 3 different levels, for one country. Each level 3 variable has a different weight, as indicated on the weight column.

I want to create a graph that will allow me to explore the 3 different levels with the drill down capability. However, my attempts always ended with the wrong data aggregation (ie. PowerBI not taking into account the need to rebalance the weights when moving from one level to another).

 

To illustrate my issue, an example of what the correct data aggregation would be: if I want to see the score for variable A for country XX, a simple sumproduct (Score*Weight), with the interval of lines 2 to 5  would do the trick. The corresponding score will be 3,10. For variable AB, PowerBI needs to rebalance the weights so that the three corresponding weights (lines 2, 3 and 4) add up to 1, and the correct score would be 2,85. For variable ABA, PowerBI would need to rebalance the two weights (lines 2 and 3) so that they would add to 1 and calculate the corresponding score, which would be 2,27.

 

Thanks in advance for anyone who can help me

 

CountryVariable Level 1Variable Level 2Variable Level 3WeightScore
XXAABABA0,21
XXAABABA0,353
XXAABABC0,404
XXAAAAAA0,055
XXBBABAA0,40
XXBBABAB0,11
XXBBBBBA0,26
XXBBBBBA0,152
XXBBBBBC0,153
8 REPLIES 8
Super User III
Super User III

Hi,

Your question is not clear.  How did you arrive at the figures of 3.10 or 3.00?  What do you mean by "rebalance the weights"?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User IV
Super User IV

@thomas_v , not very clear.

Have you tried a measure like

sumX(Table,Table[Score]*Table[Weight])

 

If need different calculation at different level : 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!

Hi Ashish_Mathur, thanks for your reply!

Let me show you an example of what I am trying to replicate in PowerBI: I want to be able to calculate the weighted score for each variable, given the 3 different levels. For example, the weighted score for variable A (Level 1) will be a simple sumproduct (0,2*1+0,35*3+0,4*4+0,05*5). Now, for variable AB (Level 2), I have to rebalance the weights because they won't add up to 1 (0,2 + 0,35 + 0,4). Once I rebalance the weights, I can do a sumproduct between the 3 new rebalanced weights and the corresponding score, which would be (0,21*1+ 0,37*3 + 0,42*4), giving me a result of 2,85. For a level 3 variable, let's say variable ABA, I would also have to rebalance the weights so they add up to 1 in order to do a sumproduct (0,36*1 + 0,64*3), giving me a result of 2,27.

 

Please let me know if these examples are not clear. 

Thanks for the attention!

Hi,

That is exacy what my question is.  What logic do we have to use the rebalance the weights from 0.20 to 0.21 etc.  Explain that very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish_Mathur, 

The logic we need to use is the proportionality of the weights within the level we want to calculate the weighted score.

Example: To rebalance the weights for calculting the score for variable AB, the formula is (Weight AB)/(Sum of all Weights for AB):

1. (0,2)/(0,2+0,35+0,40) = 0,21 for the first weight

2. (0,35)/(0,2+0,35+0,40) = 0,37 for the second weight

3. (0,4)/(0,2+0,35+0,40) = 0,42 for the third weight

 

Hi,

Thank you for clarifying.  Can you please also share the exact result you want.  Don't share visual for now.  Just share the expected result in a Table format and let me know what selections in the slicer/filter will you be making. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish_Mathur,  thanks for your reply!

 

Please find bellow a table with the expected results. I changed the table so that the levels are now a column. As for the slicers and filters, the only one would be the country column. As for the variables and the levels, my intention is to use the drill down capability to show, initially, only variables from the level 1 and once the user clicks on a varible, it would show the corresponding variables on level 2.  

 

 

CountryLevel VariableWeighted Score
XX1A                        3,1
XX1B                        2,1
XX2AB                        3,0
XX2AA                        5,0
XX2BA                        0,2
XX2BB                        3,9
XX3ABA                        2,3
XX3ABC                        4,0
XX3AAA                        5,0
XX3BAA0,0
XX3BAB                        1,0
XX3BBA                        4,3
XX3BBC                        3,0

Hi,

Someone else will help you with this.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors