Help conditional formatting %CT (percent of column totals)

[Anonymous]

I've searched all over the place and haven't figured out how to conditionally format values based on % of column totals in my matrix table.  I thought this would be simple!  I just would like to highlight anything 5% or greater.  Can anyone help?

[v-lili6-msft]

hi, @Anonymous 

You could use this logic to create a %CT (percent of column totals) measure

percent of column totals = 
DIVIDE (
    CALCULATE ( SUM ( 'Table'[Qty] ) ),
    CALCULATE ( SUM ( 'Table'[Qty] ), ALLSELECTED ( 'Table'[YEAR(Rews)] ) )
)

Then use this formula to conditional formatting the matrix

and in my simple sample pbix file, I would like to highlight anything 50% or greater

and here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

Here's the visual of what I'm seeing.  Notice how many of the highlighted values are less than 5%.  I would just like to highlight those that are 5% or higher.

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

Please just change the "Percent" to "Number" in the drop-down under Rules

The way the "Percent" conditional formatting works is different than you think, it doesn't have anything to do with percent of total. It take the lowest and hightest displayed amounts and uses those to determine the formatting bands based on the Percent you entered so your table is looking at these numbers to apply the formatting.

Regards,

Lin

[Anonymous]

That worked!  Thanks!

[Anonymous]

Thanks, Lin.  However, when I create the rule to highlighted anything greater than or equal to 5%, it highlights many numbers less than 5% as well.  I would send a screen shot but not sure what to enter in the source fields, dimensions, etc.