Forum Discussion

Donny620's avatar
Donny620
Helper I
2 years ago
Solved

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 o...
Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    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 🙂






Donny620's avatar
Donny620
Helper I
2 years ago

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's avatar
Fowmy
Super User
2 years ago

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.