Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I'm really having a hard time averaging the sum of payments for different shifts. The table has columns like this:
Service Date | Shift Name | Payout |
1/1/2024 | Shift One | 25.00 |
1/1/2024 | Shift One | 25.00 |
1/5/2024 | Shift One | 12.50 |
1/10/2024 | Shift Two | 30.00 |
1/10/2024 | Shift Two | 15.00 |
So, I want to calculate the average that someone has made for Shift One and Shift Two. what I can see for Shift One they made an average of $20.83 based on adding 25,25,12.50 and divide by three.
For Shift two they made $22.50 on average.
Looking at doing an "average of sums" I created a measures like this:
SumPayout = SUM(Table[Payout])
SumPayoutAvg = AVERAGEX(Values(Table[Shift Name]), [SumPayout])
However, I'm getting a HUGE number when the data is displayed. I can't understand how it's getting that number but it's quite large.
So, I'm obviously not calculating this right. Does anyone have an idea of how I do this?
I'm sorry my original explanation was bad. I have a reply up here explaining further if anyone would like to take a crack at it.
Hi @Thomas_MedOne ,
Please follow these steps:
1. This is the original data I created.
2. Please try:
SumPayoutAvg = AVERAGEX(
FILTER(
ALL('Pay'),
'Pay'[Shift Name] = MAX('pay'[Shift Name])
),
'Pay'[Payout]
)
3. After creating the measure, drag it to the report page for display.
pbix file is attached.
If you have any further questions please feel free to contact me.
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
My original ask was flawed, see my response way below. Thanks.
Is there anything wrong with using the built-in average function (i.e just drag it into your visual and summarize by avg), or a regular measure with the Average function.
I think your current function will only get you the total sum per shift name (probably where the large number is coming from?), since you're using a measure and not a calculated column.
When I drag the value into the visual, it doesn't give me an option for average. It only gives me "count".
try this approach :
divide (
[sumpayout],
count(tbl_name[shift name])
)
let me know if this works.
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
My original ask was flawed, see my response way below. Thanks.
I actually explained this wrong! What I asked for is not what I want. I should delete this thread but I'm not sure how.
So, in the table above, it's not that each one is treated all the same. This is what I need:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |