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

[Thomas_MedOne]

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?

[Thomas_MedOne]

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]

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!

[Thomas_MedOne]

My original ask was flawed, see my response way below. Thanks.

[vicky_]

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.

[Thomas_MedOne]

When I drag the value into the visual, it doesn't give me an option for average. It only gives me "count".

[Daniel29195]

@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! 🤠

[Thomas_MedOne]

My original ask was flawed, see my response way below. Thanks.

[Thomas_MedOne]

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?