Conditional formatting based on percentile value

[bhuprakashs]

Hi Experts,

I have a requirement to implement color coding in table metrics based on 50% percentile. (below pic from excel)

Result in Excel:

Same conditional formatting I want to implement in powerbi but not able to make it. I have tried below in Powerbi not desired output is not coming because I am using 50 percent not the percentile. I am not aware how to calculate percentile in powerbi.

PowerBI Implementation by me:

Kindly help me how can I achieve this . Thank you

[bhuprakashs · Accepted solution]

thanks @Bibiano_Geraldo  for the solution but unfortunately is not working in my case because I have do not have direct column to use in formula. there is already measure created from multiple measures so giving incorrect result for me.

[Bibiano_Geraldo]

Hi @bhuprakashs ,
Power BI has a built-in function called PERCENTILEX.INC, which calculates percentiles. For the 50th percentile (median), you can create a measure like this:

Percentile 50 = 
PERCENTILEX.INC(
    ALL('Table'[Column]),  -- Replace 'Table' and 'Column' with your table and column names
    'Table'[Column],       -- The column you want the percentile of
    0.5                    -- 50% percentile (for the median)
)

Now that you have the 50th percentile, you can use it to create a measure for conditional formatting. For example:

Color Code Measure = 
VAR MedianValue = [Percentile 50]
VAR CurrentValue = SUM('Table'[Column])  -- Replace with your metric column
RETURN
    IF(CurrentValue < MedianValue, 1, 
        IF(CurrentValue = MedianValue, 2, 3))

1 could represent a value below the median (you can assign a color like red).

2 could represent a value equal to the median (use a neutral color like yellow).

3 could represent a value above the median (you can assign a color like green).

This way, you'll achieve a similar color coding in Power BI as you had in Excel based on the 50th percentile.