Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Donny620
Helper I
Helper I

Custom Conditional Formatting for Multiple Columns?

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:

 

Donny620_0-1701199696783.png

Excel:

Donny620_1-1701199706028.png

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!

1 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.

Fowmy_0-1701262081129.png

The file is attached below 🙂






Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

6 REPLIES 6
Donny620
Helper I
Helper I

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. 😞

@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.

Fowmy_0-1701262081129.png

The file is attached below 🙂






Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@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!

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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:

 

Donny620_0-1701290991116.png

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!

@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:

Fowmy_0-1701326402511.png


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_1-1701326476422.png

 

 




Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Fowmy
Super User
Super User

@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.


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.