Custom Conditional Formatting for Multiple Columns?

[Donny620]

Hi! I've created a sample PBI for my problem, and I'm sorry got it too late and now it's buried in the other thread, if you need to delete this thread please just let me know.  I've looked at a ton of youtube videos for help but can't find help for my specific problem.

The problem: I want to make a PBI table where each column is a measure look like the Excel version of the same table (which uses the default Excel 50 percentile conditional formatting):

PBI:

Excel:

If you look at Type 1 column for rows 2022-2023 for example, the Excel shows much more varialbity than the PowerBI, because the Excel is using the 50 percentile (i.e. the PBI is showing barely any difference between the lower values)  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 set to automatic.

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

Thanks!

[Donny620]

Thanks Fowmy!  I think there might be a way through the PERCENTILEX.EXC measure, like in this thread:

https://community.fabric.microsoft.com/t5/Desktop/Background-color-variation-in-conditional-formatti...

But I can't figure out how to duplicate this because you can't run PERCENTILEX.EXC on measures, so I think I need to maybe make a virtual table or actual table but I can't figure it out within the file I have attached above. 😞

[Fowmy · Accepted solution]

@Donny620 

I referenced the answer of  @MFelix and resolved your problem, I hope it should work for you. I also created a calendar table. 

Here is the measure that the conditional formatting is based on:

CF TYPE 1 = 
VAR __50PCT = PERCENTILEX.EXC( SUMMARIZE( ALLSELECTED( Sheet1 ) , Dates[Year] , Dates[Qtr], Dates[QtrNo] ), [Type 1] , 0.5 )
VAR __T1 = [Type 1]
VAR __RESULT = IF( NOT ISBLANK( __T1) , __T1 - __50PCT )
RETURN
    __RESULT
    

Result: I added CF based on Power BI default settings on a copy of TYPE 1 measure and renamed to TYPE 1 Comp for comparison.

The file is attached below 🙂

[Fowmy]

@Donny620 

Please mark the answer as accepted if it works for you so that other will find it useful.
Do let me know if you need further assistance on this.

Cheers!

[Donny620]

Hi @Fowmy thank you so much!  I believe this has solved my problem!  One problem though, now that the calendar table was added (was this just good practice or was it needed for the conditional formatting?), my other table which shows the results by month is not showing the correct sorting:

I held down shift and sorted by year, then quarter, then month, but the month portion is sorting alphabetically instead of by calendar.  Is the only way to fix this to have month be numbers?

As you can tell, I had to adjust the date table to include month:

Dates =

ADDCOLUMNS(

    CALENDARAUTO(),

    "Year", YEAR([Date]),

    "QtrNo" , QUARTER( [Date] ),

    "Qtr", "Qtr "& QUARTER( [Date] ),

    "Month", FORMAT ( [Date], "mmm" ),

"Month Number", MONTH ( [Date] )

)

Thanks!

[Fowmy]

@Donny620 

Having a dates table is a good practice as well as it resolves many issues in DAX and modeling. 
You can also resolve your problem without having one but I would not do that. 

When you create a dates table, ensure you turn off the Auto Date/Time in the settings as follows:

Then, select each column that are text type based and sort it by respective column like you can sort the month name by month number.  

[Fowmy]

@Donny620 

Regrettably, Power BI currently lacks the capability to dynamically assign values to its conditional formatting, especially concerning gradients, using measures. I am hopeful that the Power BI team will address this limitation in the near future.

I found an idea posted in 2020 and we are wating for this features to be delivered: Microsoft Idea
Please vote for it.