Graphing weighted average by day

[ebecerra]

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:

Date	Machine	Score	Session Count
18-Jan	A	26	145000
19-Jan	A	21	142936
18-Jan	B	1056	2383733
19-Jan	B	995	2242522

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:

Date	Weighted Average
18-Jan	996.9388022
19-Jan	936.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!

[dax]

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]

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

[OwenAuger · Accepted solution]

@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