cancel
Showing results for
Search instead for
Did you mean:
Helper III

## Calculate Average of Fiscal SUM per class/category

Hello! I could use some help trying to come up with a measure to calculate the average of SUM (labor & material) by fiscal year per class/category, I believe I would need to use SUMX or AVERAGEX? Any help would greatly appreciated!

1 ACCEPTED SOLUTION
Community Support

Hi @mrmiyagi ,

Please try this:

``````AVG =
AVERAGEX (
ALLEXCEPT ( Labor, Labor[Asset Class], Labor[Asset Category] ),
[SumLabor_Material]
)
``````

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User IV

@mrmiyagi

Your two measures for average labor and material should be: (Correct the Table and Field names)

``````Avg Labour =

AVERAGEX(
SUMMARIZECOLUMNS(
'BookedMaterial'[Category],
'BookedMaterial'[Class],
'Date'[Fiscal Year]
),
SUM('Booked Labour_Merged'[Labour Cost])
)

Avg Material =

AVERAGEX(
SUMMARIZECOLUMNS(
'BookedMaterial'[Category],
'BookedMaterial'[Class],
'Date'[Fiscal Year]
),
SUM('Booked Labour_Merged'[Material Cost])
)
``````

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up

Website   YouTube    LinkedIn   PBI User Group

Helper III

Thank you for the response!

I noticed when I add one of the AVG measure to a chart/visual it throws an error

In addition all of the numbers are the same from SUM no change and do not see an Average

Community Support

Hi @mrmiyagi ,

Please try this:

``````AVG =
AVERAGEX (
ALLEXCEPT ( Labor, Labor[Asset Class], Labor[Asset Category] ),
[SumLabor_Material]
)
``````

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

Thank you this worked perfectly!

## Helpful resources

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Check it out!

Click here to read more about the July 2021 Updates

#### Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

#### Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors