cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrmiyagi
Helper III
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! 

 

table_data.PNGrelationship_table.PNG

1 ACCEPTED SOLUTION

Hi @mrmiyagi ,

 

Please try this:

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

 

v-xuding-msft_0-1597390711481.png

 

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.

View solution in original post

4 REPLIES 4
Fowmy
Super User IV
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


Blog - Data Visualization

Website   YouTube    LinkedIn   PBI User Group

Thank you for the response! 

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

 

error.PNG

 

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

 

total_avg.PNG

Hi @mrmiyagi ,

 

Please try this:

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

 

v-xuding-msft_0-1597390711481.png

 

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.

View solution in original post

Thank you this worked perfectly! 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

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

Top Solution Authors
Top Kudoed Authors