topic % of Row Total DAX Measure Only Working Sometimes in DAX Commands and Tips

% of Row Total DAX Measure Only Working Sometimes

GarlonYau — Wed, 28 Sep 2022 20:44:16 GMT

Hi There and thanks in advance for your help.

I'm trying to replicate the "Show value as % of Row Total" as a DAX measure. This is so I can perform column specific target number comparisons and thus add conditional formatting logic.

As seen below, the TOP table is the end result I wish to have. The BOTTOM table are the source numbers.
"Emp Name (Group)" is the field for the rows, and "Mapping" is for the columns.

Here's the formula I'm using:

Measure = CALCULATE([Total Hrs],ALLSELECTED('Labour Detail'[Mapping]))
Measure = CALCULATE([Total Hrs],ALL('Labour Detail'[Mapping])) < I've also tried this one and it doesn't work.

and here's the result:

I feel like I'm really close. The weird part is that when I switch the measure over to use a different field called "Period", and swap out my columns in the matrix to period, it ends up working:

Measure 3 = CALCULATE([Total Hrs],ALLSELECTED('Labour Detail'[Period]))

As you can see, the Measure 3 is giving me the total of the row.

Anyone have an idea as to why this DAX measure seems to only work with my "Period" field but not my "Mapping" field?

Re: % of Row Total DAX Measure Only Working Sometimes

GarlonYau — Thu, 29 Sep 2022 06:30:09 GMT

Any ideas or suggestions on this would be greatly appreciated!

Re: % of Row Total DAX Measure Only Working Sometimes

v-xiaosun-msft — Thu, 29 Sep 2022 10:39:40 GMT

Hi GarlonYau ，

Could you tell me what’s your 'Labour Detail' table? According to your description, here is my solution.

Create a column.

Column = 'Table'[SALES] + 'Table'[SALES-P#] + 'Table'[MANAGEMENT] + 'Table'[ADMINISTRATION] + 'Table'[R&D] + 'Table'[TECHNICAL] + 'Table'[SAFETY] + 'Table'[CHARGEABLE]

Create measures.

Measure = DIVIDE ( MAX ( 'Table'[SALES] ), MAX ( 'Table'[Column] ) ) Measure2 = DIVIDE ( MAX ( 'Table'[SALES-P#] ), MAX ( 'Table'[Column] ) ) Measure3 = DIVIDE ( MAX ( 'Table'[MANAGEMENT] ), MAX ( 'Table'[Column] ) ) Measure4 = DIVIDE ( MAX ( 'Table'[ADMINISTRATION] ), MAX ( 'Table'[Column] ) ) Measure5 = DIVIDE ( MAX ( 'Table'[R&D] ), MAX ( 'Table'[Column] ) ) Measure6 = DIVIDE ( MAX ( 'Table'[TECHNICAL] ), MAX ( 'Table'[Column] ) ) Measure7 = DIVIDE ( MAX ( 'Table'[SAFETY] ), MAX ( 'Table'[Column] ) ) Measure8 = DIVIDE ( MAX ( 'Table'[CHARGEABLE] ), MAX ( 'Table'[Column] ) ) Measure9 = DIVIDE ( MAX ( 'Table'[Column] ), MAX ( 'Table'[Column] ) )

Final output:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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

Re: % of Row Total DAX Measure Only Working Sometimes

GarlonYau — Fri, 30 Sep 2022 17:37:57 GMT

Thank you for the suggestion, but I don't think that would work for the conditional formatting logic that I was trying to create after figuring out this part of the formula.

SOLUTION:

I did however find that the reason it wasn't working was because I had created a "Sort" column to sort the Mapping column. I removed the sort from the mapping column and it ends up working.