Power Bi robust conditional formatting like Excel

[bkxn226]

I've been using Power BI quite a bit and really like it; however, the most frustrating part is the condtional formatting within tables.  I want to make sure I'm not missing somethign obvious or if there is a visual I can purcahse that can get the job done for me.  When I try to conditionally format in Power BI, it does Lowest/Highest and only lets you custom by Number, not Percentile.  Here is what a basic table looks like, and its clearly not robust to outliers.  

A similar table in Excel is robust to outliers since by default it uses a percentile as the middle value for the gradient.  It doesn't matter what I change the 100000 

For instance, instead of Lowest/Highest value, is there a way to have it automatically be robust to outliers, like it is in Excel.  Again, the way it acheives this is by using percentiles, specifically having the midpoint as percentile of 50%.  

I understand you can potentially write your measure but is there any obvious option that I am missing?  And if you can write a measure is there a trick to make it scalable?

How is something so basic not available in Power BI?  Is there a custom table visual I can even buy that can do this?   Any help would be much appreciated!

[parry2k]

@bkxn226  I don't think  @Anonymous  understood your original question and no reason to provide that reply if I already provided the same solution. Simple answer, there is no percentile out of the box but you can do your own measure to control the color. 

[Anonymous]

Hi  @bkxn226  ,

Here are the steps you can follow：

You can customize the value to change the color

1. Visualizations – Conditional formatting – Background color

2. Select the Color scale – Based on field selected field in Format by – check Doverging, when the divergence is turned on, an additional color selector, in which you can customize the value and color according to your own data

3. Result.

You can downloaded PBIX file from here.

This is related content, you can check this link:

https://docs.microsoft.com/en-us/power-bi/visuals/service-tips-and-tricks-for-color-formatting

Best Regards,

Liu Yang

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

[bkxn226]

The problem is that you are saying the center is 0 not 50% percentile.   In excel and other bi tools, you can do it by percentile (in fact, it is even the default).  Is there really no way to do it in power bi?

[parry2k]

@bkxn226, not I'm aware of but you can built that percentile logic in a measure and create your own color and then use that in conditional formatting. Surely it is extra work, and I'm very hopeful this will come in Power BI at some point in time. Good luck!

[parry2k]

@bkxn226 it doesn't have scaling based on percentile, either write your own measure or turn the diverging option on scaling. 

I would recommend adding this as an idea on the ideas forum, if it already exists, upvote for it.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[bkxn226]

ugh, how is something as basic as this and implemented in Excel an idea rather then default?  is there something you know of I can buy?