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!

Proud to be a Super User!

Website   YouTube    LinkedIn

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

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors