Forum Discussion
Data aggregation issues for different hierarchical levels
Super UserHi,
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"?
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!
- Ashish_Mathur
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.
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
- Ashish_Mathur
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.