Background color variation in conditional formatting of cells to match Excel?

[Donny620]

Hello!

I'm trying to follow the instructions in this older thread here.  

I have basically the same problem as the poster there, I'm trying to duplicate columns that have conditional formatting in Excel, into PowerBI.  The problem is that PowerBI by default doesn't give enough contrast between green and yellow (I need it to follow the Excel rule of 50 percentile).  I can't share the file but the data looks like this in PowerBI: 

*Note each column in the PBI table is a measure.

*Note that I can't just change the PBI conditional formatting center value to be 'custom' set to about the average of each column, because there will be filtering where the values can severerly change, hope that makes sense.  The Min, Center, and Max values of the conditional formatting must be lowest value, middle value, and highest value.

Ignoring slightly different numbers the Excel looks like this (for example first column in the PBI is too 'green' it needs more yellow like the Excel):

So I tried to duplicate the two formulas in the linked post above:

percentile =PERCENTILEX.EXC(ALL('Table'),[Measure],0.5)

Percentile_Variation = ('Table'[Measure])-[percentile]

*Where I put in my table for Table and my measure name for Measure.

*Note: the link where I got these formulas I think was based on a calculated column not a measure like my data, so I removed the SUM part of Percentile_Variation, if this formula isn't right please let me know.

Since I have so many measures (each column is a measure), I think I would have to make ~13 versions of these measures.  But anyway if I try to test just one by putting in my table name and measure name, then change the conditional formatting to be based on 'Percentile_Variation', the table goes blank and says it can't display the values.  

So, am I doing something wrong?  Is there an easier way?

[Donny620]

Hello!

I have made a sample file that illustrates my problem, here it is:

https://drive.google.com/file/d/14fskb352Uh-OHtFmd5c8QWcajyk_FaOW/view?usp=sharing

Here is the PBI view:

Here is the Excel view:

If you look at Type 1 2022-2023 for example, the Excel shows much more varialbity than the PowerBI, because the Excel is using the 50 percentile.  My question is: how can I use DAX (or any other solution) to get closer to the Excel view?

Please note: my actual data has 13 measures (columns), and the issue with Type 1 is more apparent in the actual data.  Also I can't just adjust the conditional formatting middle value because the actual data has filters, so the middle value has to be automatic.

Thank you!

[parry2k]

@Donny620 check it these 2 videos on my channel helps:

Developing Custom Gradient Color Scales for Columns in Matrix Visuals in Power BI: Part 1 - YouTube

How to create your own color gradient scale for each column in a matrix visual - Part 2 - YouTube

[Donny620]

Hi Parry thank you so much for sharing, those videos were very helpful to give me context.  Unfortunately both of the use cases are very specific and I can't see how I can change it to fit what I need.  I just need to mirror the Excel rule of 50 percentile.  But my requirements are:

*Each column in the PBI table is a measure.  There are 13 columns/measures in the PBI table.  Each column has conditional formatting as each is independent in terms of the color scale.  

*I tried to change the PBI conditional formatting center value to be 'custom' set to about the average of each column, I found that the result is close to the Excel 50 percentile.  However because there will be filtering on the table, so this means this solution won't work because if I drill down the values/scale changes and it doesn't look right

So I think I need measures that changes the color scale to 50 percentile, like the Excel.

As I mentioned above, there was another post very similar to mine with a PBI that I tested but didn't work for me (see above).

Any ideas?
Thanks so much!