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 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!

 

 

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.

     

4 Replies

    • 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.