Forum Discussion

nsingh07's avatar
nsingh07
Icon for Helper I rankHelper I
5 years ago

Urgent 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 th...
Need Help
v-lionel-msft's avatar
v-lionel-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi nsingh07 ,

 

Please refer to my .pbix file.

 

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

nsingh07's avatar
nsingh07
Icon for Helper I rankHelper I
5 years ago

v-lionel-msft  Ashish_Mathur 

 

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/110
 
The denominator should get updated with every slicer selection. For eg: if I deselect B from Level 4, the weighted ratio should be 100/100
ie  
(Revenue corresponding to A)/ (sum of revenue corresponding to "AA" (Sum of Level 4-A))
 
TIA