Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II
Helper II

Average a Sum. I have tried the standard; not working

I'm really having a hard time averaging the sum of payments for different shifts. The table has columns like this:


Service DateShift NamePayout
1/1/2024Shift One25.00
1/1/2024Shift One25.00
1/5/2024Shift One12.50
1/10/2024Shift Two30.00
1/10/2024Shift Two15.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?

Helper II
Helper II

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.

Community Support
Community Support

Hi @Thomas_MedOne ,


Please follow these steps:


1. This is the original data I created.



2. Please try:

SumPayoutAvg = AVERAGEX(
        'Pay'[Shift Name] = MAX('pay'[Shift Name])


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,
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.

Super User
Super User

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".

Super User
Super User


try this approach : 

divide ( 


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:


  • A doctor is working a shift and he enters in charges for patients. He may do 10 charges, he may do 5, whatever.
  • If he only works ONE day in the month and one shift. Then the Payout value I want displayed is the SUM of all the charges he made that day.
  • So, if he worked only one day and one shift and billed $50 5x then the display amount is $250
  • If he worked two shifts and billed 5x $50 on both shifts, the output would still be $250 because that's the average total he billed for two shifts.
  • So, I need to total up all the charges. Then average that total dollar amount by each shift he worked. So I guess that's The date of the shift, right?

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.