DAX formula to calculate sum of averages by category dynamically

[chiragjagga]

I have a table with Columns- Module, Subcategory, Num_of_users, Cost per user, Total Cost etc. I am using slicers with option to multi-select subcategory column values. I am able to create a Tile card to display average cost per unique combination of [Module+Subcategory], but facing issue when trying to select multiple values in the slicer for Subcategory.

Example of the formula being used here to calculate Total Cost for slicer value selection: 

SUMX('Data Refined','Data Refined'[Average Quoted price ]* 'User Count Parameter'[User Count Parameter Value]/COUNT('Data Refined'[Sub-category]))

The above formula works great for one value at a time but fails with multi-select because the Average Quoted Price takes iny the average price for all the rows that match with selected slicer values.

I have to calculate average dynamically based on any number of selections in that slicer, please suggest modification into the current formula or a new formula altogether to achieve this

[chiragjagga]

Hi Tamerj1,

Thankyou for your suggestion but this formula isnt working correctly, more explanation below-

This formula takes the sum of the average values per category first, then takes an overall average....what happens in this case is lets say there are 2 rows for Category A with values 800 and 1000, 1 row for Category B with value 1200, then this formula is taking SUM of [Average of price for Category A=900, 2 times] + [Average of price for Category B=1200, 1 time] which comes out to be 3000, then taking another Average due to the outside function, and dividing (3000/2)=1500, because it finds count of distinct categories=2.

I am looking to take Sum of averages for individual category dynamically, based on slicer selection, so the desired number should be 900+1200=2100 for avg of 2 categories

[tamerj1 · Accepted solution]

@chiragjagga 

In this case please try

please try

Total Cost =
SUMX (
VALUES ( 'Data Refined'[Sub-category] ),
CALCULATE (
AVERAGEX (
'Data Refined',
'Data Refined'[Average Quoted price ] * 'User Count Parameter'[User Count Parameter Value]
)
)
)

[tamerj1]

Hi @chiragjagga 

please try

Total Cost =
AVERAGEX (
VALUES ( 'Data Refined'[Sub-category] ),
CALCULATE (
SUMX (
'Data Refined',
'Data Refined'[Average Quoted price ] * 'User Count Parameter'[User Count Parameter Value]
)
)
)