cancel
Showing results 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

## 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 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?

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

1. This is the original data I created.

``````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!

Helper II

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

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.

Helper II

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

Super User

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

Helper II

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

Helper II

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors