Forum Discussion

ebecerra's avatar
ebecerra
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago
Solved

Graphing weighted average by day

I'm having a problem trying to graph the weighted average of my data. I've already tried to look in the forums for similar scenarios but didn't really find anything that I could use as baseline. Let me explain what I have in a short summarized version of my table (I will add slicers to analyze the data much better, like Machine but there are other fields).

 

Table:

DateMachineScoreSession Count
18-JanA26145000
19-JanA21142936
18-JanB10562383733
19-JanB9952242522

 

In order to get the weighted average I added the following measure:

 

WAvg = DIVIDE(
    SUMX ( 'Table', 'Table'[Score] * 'Table'[Session Count] ),
    CALCULATE ( 
        SUM ( 'Table'[Session Count] ), 
        ALLSELECTED ( 'Table' ) 
    )
)

 

 

Which does give me the right Weighted Average for Everything in the table. However, I wanted to graph the daily weighted average. This is where I'm probably not doing something right, since just selecting the Date and the new measure on the chart gives me the average of the weighted average (or something strange like that):

The sum of these two values IS the correct weighted average for all of the data in the table. However I was expecting to see the daily Weighted Average to be:

DateWeighted Average
18-Jan996.9388022
19-Jan936.6381827

 

What am I doing wrong? Do I need a different measure to calculate the weighted average on a daily basis or something like that?

 

Thanks!

Need Help
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    ebecerra 

    There shouldn't be any need to use ALLSELECTED in your Wavg measure.

    The denominator should be simply the sum of Session Count in the current filter context.

     

    Try this:

    Wavg = 
DIVIDE (
    SUMX ( 'Table', 'Table'[Score] * 'Table'[Session Count] ),
    SUM ( 'Table'[Session Count] )
)

    This should give the correct weighted average whether filtered by Date or anything else.

    Does this give the correct result?

     

    Regards,

    Owen

3 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    7 years ago

    ebecerra 

    There shouldn't be any need to use ALLSELECTED in your Wavg measure.

    The denominator should be simply the sum of Session Count in the current filter context.

     

    Try this:

    Wavg = 
DIVIDE (
    SUMX ( 'Table', 'Table'[Score] * 'Table'[Session Count] ),
    SUM ( 'Table'[Session Count] )
)

    This should give the correct weighted average whether filtered by Date or anything else.

    Does this give the correct result?

     

    Regards,

    Owen

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi ebecerra,

     

    You could try to use below measure to get your result

    WAvg1 = DIVIDE(
    SUMX ( 'Table', 'Table'[Score] * 'Table'[Session Count] ),
    CALCULATE ( 
        SUMx ('Table', 'Table'[Session Count] )
        
    )
)

    You could compare

    Measure 2 =
CALCULATE ( SUMX ( 'Table', 'Table'[Session Count] ) )

 and 
Measure 3 = CALCULATE (  SUM ( 'Table'[Session Count] ),  ALLSELECTED ( 'Table' )  )

    Measure 3 will return total in each row, and measure 2 will calculate total based on current context

    Best Regards,

    Zoe Zhi

     

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

    • ebecerra's avatar
      ebecerra
      Icon for Microsoft Employee rankMicrosoft Employee
      7 years ago

      Thanks OwenAuger ! That seemed to do the trick and all my slicers work just fine.