Conditional formatting for % of row total

[mhese]

Hi all,

New to PowerBI, I have the below matrix where i'm trying to apply conditional formatting to the % of row column (green if above 95%, red if below 95%).

The % of Row column was added through the "Show Value As" method, as pictured below.

When I apply the below conditional formating:

My matrix looks as below:

I suspect it has something to do with the two below form fields:

However summarisation and "what field should we base this on?" don't seem to have viable options to select the row total % I need.

Another issue I have is that I would need the conditional formatting to just apply to the "Pass" column for % of Row.

I've looked through 5-6 youtube videos on conditional formating for matrix, but they did not touch on %'s like this.

Does anyone have any pointers, is my approach wrong, how would I go about creating the intended visual?

Constructive criticism is welcome too, I really only need a visual to show me the volume and Pass Percentage per month by region. If this is better handled through something other than a matrix then I'm happy to try that.

[Anonymous]

Hi @mhese ,
Thanks for @dharmendars007 reply.
You can create two mesures

% of Count = 
VAR _Value = SUM('Table'[Count])
VAR _Total = 
CALCULATE(
    SUM('Table'[Count]),
    ALLEXCEPT(
        'Table',
        'Table'[Destination Zone],
        'Table'[Order Date].[Month]
    )
)
RETURN
_Value/_Total

Color = 
IF(
    SELECTEDVALUE('Table'[SLA Outcome]) <> "Pass",
    "No color",
    IF(
        [% of Count] < 0.95 ,
        "Red",
        "Green"
    )
)

Apply the Color to conditional formatting

Final output

Best regards,
Albert He

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

[mhese]

Hi Albert,

Sum Didn't work in the suggested measure as the values for SLA outcomes are strings.

I'm currently using the below:

% of Pass Only =
VAR _CategoryCount =
COUNTROWS('Data Model')

VAR _TotalCount =
CALCULATE(
  COUNTROWS('Data Model'),
  ALL('Data Model'[SLA Outcome])
)

RETURN
IF(
  SELECTEDVALUE('Data Model'[SLA Outcome]) = "pass",
  DIVIDE(_CategoryCount, _TotalCount, 0),
  BLANK()
)

This is giving me the below outcome:

This is still not the desired outcome though, I would need the count of each outcome (Fail, Pass and Undelivered), and then just a singular column with the SLA %, I can't figure out how to achieve this.

I also attempted to use this measure in the gauge visual, but it is only returning blank.

I don't think the measure I created is good outside of the matrix above.

I've tried adapting the measure you've given above, but I can't work out how to get it to work.

What I'm trying to get is the SLA pass percentage for any given filters added to a visual, is that at all possible, or do the measures need to be created with specific filters in mind?

[Anonymous · Accepted solution]

Hi @mhese ,
Thank you for your reply, Based on the mesure you provided, SELECTEDVALUE('Data Model'[SLA Outcome]) = “pass” is just used to determine that your custom format is being used on that line, you don't need to use this expression in the MEASURES you use to calculate percentages.

% of Pass Only =
VAR _CategoryCount =
COUNTROWS('Data Model')

VAR _TotalCount =
CALCULATE(
  COUNTROWS('Data Model'),
  ALL('Data Model'[SLA Outcome])
)

RETURN
DIVIDE(_CategoryCount, _TotalCount, 0),

You can follow the steps of the two MEASURES I created to use. If this did not help, we can't simulate the data you're using, if you'd like more accurate help, please provide example data or a pbix file, and also present your results as an image or excel, so we can help you faster. Please hide sensitive information in advance.

Best regards,
Albert He

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

[dharmendars007]

Hello @mhese , 

Instead of Selecting show as Values please create the below % Measure..

Percent of Row =DIVIDE(SUM(Sales[SalesAmount]),
CALCULATE(SUM(Sales[SalesAmount]),ALLSelected(Sales[Region])))

Next you can create a Condition mesure like below and go to  >> Format Style >> Select option >> Field values and select the below measure to apply the formatting.

Color Coding =
SWITCH(
TRUE(),
[Percent of Row] > 0.95, "Green", // Apply green when the percentage is above 95%
[Percent of Row] <= 0.95, "Red", // Apply red when the percentage is 95% or below
"No Color" // Default fallback if no condition is met (optional))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN