Creating a sum based on averages

[rjaramillo]

Hello,

I have a category column, subcategory column, and a cost column.  I have a slicer to choose a specific category, and I have a chart below it that showcases the average cost for each subcategory.  I am trying to see the total sum of the average cost but the resulting total is not what I'm expecting.  

The formula I'm using is SUMX(VALUES([subcategory column]),AVERAGE([cost column]))

Below is a snippit of what I'm getting.  The left column is the standard Average of the cost column and the right column is the calculated measure I'm trying to create.

Any help would be super.  Thanks!

[danextian]

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

[rjaramillo]

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

[v-rzhou-msft · Accepted solution]

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rjaramillo]

the CALCULATE part is what I was missing!  Thank you so much!