Forum Discussion
Creating a sum based on averages
Hi rjaramillo ,
I think there are two kinds of ways to create measures to calculate correct average total.
Measure1:
Measure 2 = SUMX(VALUES('Table'[subcategory]),CALCULATE( AVERAGE('Table'[cost])))You need to add "CALCULATE" function before "AVERAGE", it will consider filter context and give you correct result.
Measure2:
Measure1 = VAR _SUMMARIZE = SUMMARIZE('Table','Table'[subcategory],"Avg by subcategory",CALCULATE(AVERAGE('Table'[cost]))) RETURN SUMX(_SUMMARIZE,[Avg by subcategory])Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hI rjaramillo,
I tested your approach and the formula worked find on my end. I did get the expected result which is the sum of the average. Here is a sample pbix - https://drive.google.com/file/d/11e0N1DPmJyr7k1U2rFlwFuTvhivv3yYq/view?usp=sharing
So that's what I'm expecting as well I thought it would be pretty straightforward but maybe there's something behind the scenes? I'm not sure if there's another way to get the answer
Hi rjaramillo ,
I think there are two kinds of ways to create measures to calculate correct average total.
Measure1:
Measure 2 = SUMX(VALUES('Table'[subcategory]),CALCULATE( AVERAGE('Table'[cost])))You need to add "CALCULATE" function before "AVERAGE", it will consider filter context and give you correct result.
Measure2:
Measure1 = VAR _SUMMARIZE = SUMMARIZE('Table','Table'[subcategory],"Avg by subcategory",CALCULATE(AVERAGE('Table'[cost]))) RETURN SUMX(_SUMMARIZE,[Avg by subcategory])Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
the CALCULATE part is what I was missing! Thank you so much!