Forum Discussion
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:
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
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 🙂
6 Replies
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.Thanks Fowmy! I think there might be a way through the PERCENTILEX.EXC measure, like in this thread:
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.The file is attached below 🙂
