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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Thomas_MedOne
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?

8 REPLIES 8
Thomas_MedOne
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.

v-huijiey-msft
Community Support
Community Support

Hi @Thomas_MedOne ,

 

Please follow these steps:

 

1. This is the original data I created.

vhuijieymsft_0-1709113231822.png

 

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.

vhuijieymsft_1-1709113231827.png

 

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.

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

Daniel29195
Super User
Super User

@Thomas_MedOne 

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:

 

  • 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

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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