cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ebecerra Frequent Visitor
Frequent Visitor

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):

WAverageChart.png

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!

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Graphing weighted average by day

@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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
OwenAuger Super Contributor
Super Contributor

Re: Graphing weighted average by day

@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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Community Support Team
Community Support Team

Re: Graphing weighted average by day

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] )
        
    )
)

52.png

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

53.png

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 Frequent Visitor
Frequent Visitor

Re: Graphing weighted average by day

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

 

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 49 members 933 guests
Please welcome our newest community members: