Forum Discussion
Helper ICustom Conditional Formatting for Multiple Columns?
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 🙂
Helper IThanks 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. 😞
- Fowmy
Super User
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!