Forum Discussion
Conditional formatting based on percentile value
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
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.
2 Replies
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.
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.
