DAX calculation weighted average per category

[ilyav]

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

[Anonymous · Accepted solution]

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