% of Row Total DAX Measure Only Working Sometimes

[GarlonYau]

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?

[GarlonYau · Accepted solution]

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.

[GarlonYau]

Any ideas or suggestions on this would be greatly appreciated!

[v-xiaosun-msft]

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.