DAX calculation weighted average per category

ilyav — Thu, 05 Jan 2023 08:19:20 GMT

Hi All,

I need help with calculating a weighed average and was not able to find a solution for my use case and would appreaciate any help!

I am trying to calculate a margin weighed average per category, here is an example of the data;

Category, order, total line sale and margin are given in my dataset and I can calculate the weight = Total line Sale / SUM of all total line sales and Weighed margin = Weight x margin.

The problem that I am having now is that I want to create a visual showing the weighted average per category. So the weight column needs to dynamically adjust based on the groupping of the category, meaning that if I want to show 'category a' the 2 lines of the 'weight' need to sum up to 100% and then same for 'category b'.

In my example the correct output should be:

Category a weighted average = 39%

Category b weighted average = 51%

Is there a way to DAX it 🙂 ?

Thank you for all any help or pointers!

Ilya

Then I need

Re: DAX calculation weighted average per category

Anonymous — Fri, 06 Jan 2023 03:21:25 GMT

Hi ilyav,

I'd like to suggest you add values function with your category field in the formula and it will auto filter these calculation ranges with current category values.

formula = CALCULATE ( CALCULATE ( SUM ( 'Table'[Sales] ), VALUES ( 'Table'[Category] ) ) / SUM ( 'Table'[Sales] ), ALLSELECTED ( 'Table' ) )

Regards,

Xiaoxin Sheng

topic Re: DAX calculation weighted average per category in DAX Commands and Tips

DAX calculation weighted average per category

Re: DAX calculation weighted average per category