Forum Discussion

rjaramillo's avatar
rjaramillo
New Member
4 years ago
Solved

Creating a sum based on averages

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 subcat...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
rjaramillo
New Member
4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    rjaramillo
    New Member
    4 years ago

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