Calculate Average of Fiscal SUM per class/category

[Anonymous]

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! 

[Fowmy]

@Anonymous 


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

[Anonymous]

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 

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Please try this:

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

[Anonymous]

Thank you this worked perfectly!