How to create a summary measure across multiple columns?

[saavedrag12]

Hello, 

I'm trying to create a dynamic measure that will provide with a total headcount across mutilple fields (columns). Please see image below for example: 

I would like a measure that would identify the total headcount for a given contractor or project. I've started with this measure :

Measure_AS_Peak_HC = SUMX(SUMMARIZE(Table_Headcount,[Contractor],[Project],"Max HC", MAX(Table_Headcount[Headcount]),[Max HC])

However, it's returning the MAX HC of a contractor for a single project rather than giving me a max hc for a contractor across all projects, if that makes sense?

[v-zhangti]

Hi, @saavedrag12 

Please check the following methods.

Measure = 
CALCULATE (
    MAX ( Table_Headcount[Headcount] ),
    FILTER (
        ALL ( Table_Headcount ),
        [Contractor] = SELECTEDVALUE ( Table_Headcount[Contractor] )
    )
)

Is this the output you expect? If not, please tell the result you expect, showing it in pictures.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[saavedrag12]

Hello, 
The Calculate function does not allow me to utilize the filter function for the second argument. 

[v-zhangti]

Hi, @saavedrag12 

Is it possible to show a picture of the problem you encountered and the results you expect?

Best Regards

[vapid128]

Could you just show your result in excel?

[vapid128]

Can you just tell what number you tring to get?

max headcount of contractor of e? 141?

[saavedrag12]

I'm trying to get the total headcount that will then be adjusted when filters are applied to project and contractor.