Urgent help needed - Dynamic weighted averages based on multiple filter selections

[nsingh07]

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

[KMadsen]

@nsingh07 , did you ever find a solution to your issue? I am stuck with the same questions right now.

[v-lionel-msft]

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]

@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

[nsingh07]

FYI, 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.

@MattAllington 

[Ashish_Mathur]

Hi,

I have read your question a few times over but just cannot understand what you want.

[nsingh07]

@Ashish_Mathur 

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

[nsingh07]

Can someone please share the logic for calculating weighted average based on slicer selection

TIA