Conditional formatting for only column subtotals in matrix visual

[Anonymous]

Hello All,

I have a requirement wherein I need to highlight column subtotal for certain entries in a PBI matrix visual. On using the conditional formatting option with total only, both my column and row subtotals get highlighted (which is not the requirement).

Please assist with your responses/workaround on if there is any way to achieve highlighting for only the column subtotals. Below are my current and expected output images.

Thanks!

Current Output:

Expected Output:

[Robert14358]

calculated columns arent the greatest option, is there a community ticket for this?

[Anonymous]

Hi @Anonymous ,

There is an easy way to do this by using Pivot Column on a copy of the table in the Power Query Editor.

For example. Create a copy of the table and use Pivot column for Quarter column based on that copy.

Select FTE as Value Column.

As a result, we get the table in the previous answer, repeat the previous steps and you can get the desired result.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Anonymous]

Hi @Anonymous ,

Thanks a lot for your solution. This seems like a good alternative, however my scenario is a bit different. The only change in my case is that Quarter (Q1'22 etc.) and the matrix values are 2 separate columns. I am unable attach the PBIX file so please find the image below for the matrix column settings. Would be great if you could let me know if your solution can be modified to be used in this scenario.

Thanks!!

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please consider using calculated column instead of column subtotal. For example. Please first create a calculated column like this. and then put into a matrix with the desired columns.

Total = CALCULATE(SUMX('Table','Table'[Q1'22]+'Table'[Q2'22]))

Next assume a condition and use it as a condition for the field format.

colour = 
var _value1 = MIN('Table'[Total])
var _value2 = CALCULATE(SUM('Table'[Total]),FILTER('Table','Table'[Status]="Test1"))
RETURN
IF(_value1=_value2,"Orange")

The result should be like this.

Attach the pbix file for reference. Hope it helps.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[amitchandak]

@Anonymous ,

if(Max(Table[Status]) ="Test1" , "Orange", "White")

and use this conditional formatting using field value option

How to do conditional formatting by measure and apply it on pie? : https://youtu.be/RqBb5eBf_I4

[Anonymous]

@amitchandak 

Thanks for your response. I had already tried your approach. As mentioned, I need only the column subtotals to be highlighted. This approach will highlight both the column and row subtotals.

I have created the calculated column as you mentioned and used it for conditional formatting. Below is the image for the same. But this will highlight both the column and row subtotals. Please let me know if there is any other possible workaround.

Thanks!