topic Re: How to create a summary measure across multiple columns? in DAX Commands and Tips

How to create a summary measure across multiple columns?

saavedrag12 — Thu, 16 Jun 2022 21:11:27 GMT

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?

Re: How to create a summary measure across multiple columns?

vapid128 — Fri, 17 Jun 2022 01:27:30 GMT

Can you just tell what number you tring to get?

max headcount of contractor of e? 141?

Re: How to create a summary measure across multiple columns?

saavedrag12 — Fri, 17 Jun 2022 14:47:43 GMT

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

Re: How to create a summary measure across multiple columns?

vapid128 — Fri, 17 Jun 2022 19:22:23 GMT

Could you just show your result in excel?

Re: How to create a summary measure across multiple columns?

v-zhangti — Tue, 21 Jun 2022 02:56:57 GMT

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.

Re: How to create a summary measure across multiple columns?

saavedrag12 — Tue, 21 Jun 2022 16:43:58 GMT

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

Re: How to create a summary measure across multiple columns?

v-zhangti — Wed, 22 Jun 2022 02:39:46 GMT

Hi, saavedrag12

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

Best Regards