Forum Discussion
Helper IUrgent help needed - Dynamic weighted averages based on multiple filter selections
Hi,
Need help in getting the logic for calculating the weighted average by Revenue at different aggregtion level. The weighted average should be dynamic based on the permutation & combinations through various filter selection ( have 8 filter on my view) Stating an example below:
At level 4, its a straight forward activity. However as I aggregate at Level 3/2/1, instead of having a straight line average, I need weightage average which should be dynamic based on my filter selection
Incase, I select India in country filter, it should dynamically update the weighted average @ level 3 to 5.5 or 8 incase of US. Dynamic change should also apply if I include/exclude any row. Say, weighted average at level 2 for score 1 is 6. however this dynmically calculate if I excluded any of the level 4 based on any filter selection.
Inorder to relate the filter shown above to the example,
Business - Level 0
Pipeline - Level 1
Product Line - Level 2
Product - Level 3
Project - Level 4
Apologies, If I'm all over the place. have been breaking my head on this for past 2 days.
Looking for early help. THANKS IN ADVANCE
7 Replies
nsingh07FYI, I'm new to Power BI hence a detailed post would be very helpful.
Thanks.
Ashish_Mathur tagging you since saw few of your post on this topic.
- Ashish_Mathur
Super User
Hi,
I have read your question a few times over but just cannot understand what you want.
- nsingh07
I need help indefining a logic / DAX to calculate the weighted average score at different aggregation level (1/2/3) dynamically based on the filter selection
v-lionel-msftCommunity Support
Hi nsingh07 ,
Please refer to my .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- nsingh07
Thanks for the reply. Though this meets the desired result, UNPIVOT of data is not an option because report has multiple charts.
Instead, is it possible to get the result with a DAX calculation in the tabular form like in the example in my initial post.
For example: For Level3 weighted ratio(assume Level4 is lowest granular column), for each row I need
(revenue for that row) / (sum of revenue for corresponding Level3 value across entire dataset)
To elaborate further,
Level4 column, first row has value "A" and Revenue is "100" and corresponding Level3 value is "AA"
So, I would need:(Revenue corresponding to A)/ (sum of revenue corresponding to "AA" (Sum of Revenue for Level 4-A+Level 4-B)) = 100/110The denominator should get updated with every slicer selection. For eg: if I deselect B from Level 4, the weighted ratio should be 100/100ie(Revenue corresponding to A)/ (sum of revenue corresponding to "AA" (Sum of Level 4-A))TIA
