Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |