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.
I was able to achieve conditional formatting in a matrix by first creating the measure below:
Solved! Go to Solution.
hi, @KMcCarthy9
1. For your requirement, you want "% of CT" in the matrix, you should use ALLSELECTED([Backlog Timeframe]) in formula like below:
% of Total Workorders = VAR _FilterCount = COUNTROWS('Backlog Trend Data') VAR _AllCount = CALCULATE(COUNTROWS('Backlog Trend Data'),ALLSELECTED('Backlog Trend Data'[Backlog Timeframe - All Time])) RETURN DIVIDE(_FilterCount,_AllCount)
and you said it gives you the result of 100% in each cell.
So there should be a dim Backlog Timeframe table, please replace ALLSELECTED(workorder[Backlog Timeframe]) with it.
2. In this sample pbix, I find that there is a logic error in your formula:
% of Total Workorder Color = VAR _30DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) = "Within 30 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False) VAR _30DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) = "Within 30 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False) VAR _31DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="31 - 60 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False) VAR _31DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="31 - 60 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False) VAR _61DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time])="61+ Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False) VAR _61DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="61+ Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False) RETURN SWITCH( TRUE(), _61DaysBL && [% of Total Workorders] <=.049, "#C6EFCE",--Green _61DaysBL && [% of Total Workorders] >= .050,"#FFC7CE",--Red _31DaysBL && [% of Total Workorders] <=.099, "#C6EFCE", _31DaysBL && [% of Total Workorders] >= .010,"#FFC7CE", _30DaysBL && [% of Total Workorders] >= .850,"#C6EFCE", _30DaysBL && [% of Total Workorders] <=.849, "#FFC7CE", _61DaysCO && [% of Total Workorders] <=.300, "#C6EFCE", _61DaysCO && [% of Total Workorders] >= .319,"#FFC7CE", _31DaysCO && [% of Total Workorders] <=.700, "#FFC7CE", _31DaysCO && [% of Total Workorders] >= .710,"#C6EFCEE", _30DaysCO && [% of Total Workorders] >= .710,"#C6EFCE", _30DaysCO && [% of Total Workorders] <=.700, "#FFC7CE", "#FFF000")--Yellow
As the red part, all the values [% of Total Workorders] are greater 0.10 and less than 0.99 will all show as red("#FFC7CE"). please adjust it.
For example:
If less than 0.4 is green or greater than 0.5 is red
% of Total Workorder Color = VAR _30DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) = "Within 30 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False) VAR _30DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) = "Within 30 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False) VAR _31DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="31 - 60 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False) VAR _31DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="31 - 60 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False) VAR _61DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time])="61+ Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False) VAR _61DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="61+ Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False) RETURN SWITCH( TRUE(), _61DaysBL && [% of Total Workorders] <=.49, "#C6EFCE",--Green _61DaysBL && [% of Total Workorders] >= .50,"#FFC7CE",--Red _31DaysBL && [% of Total Workorders] <=.39, "#C6EFCE", _31DaysBL && [% of Total Workorders] >= .40,"#FFC7CE", _30DaysBL && [% of Total Workorders] >= .850,"#C6EFCE", _30DaysBL && [% of Total Workorders] <=.849, "#FFC7CE", _61DaysCO && [% of Total Workorders] <=.300, "#C6EFCE", _61DaysCO && [% of Total Workorders] >= .319,"#FFC7CE", _31DaysCO && [% of Total Workorders] <=.700, "#FFC7CE", _31DaysCO && [% of Total Workorders] >= .710,"#C6EFCEE", _30DaysCO && [% of Total Workorders] >= .710,"#C6EFCE", _30DaysCO && [% of Total Workorders] <=.700, "#FFC7CE", "#FFF000")--Yellow
Result:
Best Regards,
Lin
This is the current visual, showing percent of all.
Conditional formatting not working when choosing % of CT:
For Within 30 Days = >85% should be green
For 31-60 Days > 10% should be red
hi, @KMcCarthy9
You just need to adjust your formula
% of Total Workorders = VAR _FilterCount = COUNTROWS('workorder') VAR _AllCount = CALCULATE(COUNTROWS('workorder'),ALLSELECTED(workorder[Backlog Timeframe])) RETURN DIVIDE(_FilterCount,_AllCount)
Then don't use "select "Show as % of CT""
Result:
Best Regards,
Lin
Hi @v-lili6-msft ,
Using your formula gives me the result of 100% in each cell:
Hi again @v-lili6-msft ,
I wanted to add in that Backlog Timeframe is a conditional column. I'm not sure if that's why it's giving me trouble.
Thank you!
Hi @v-lili6-msft , I will try and supply some sample data, in the meantime I believe I just discovered more of the issue.
Using the DAX Formula you supplied, it does give me the % of CT, until I sort the Timeframe the way I want. I need to it to go in order - Within 30 Days, 31 - 60 Days, and 61+ Days. To achieve this I made a conditonal column where 1 = Within 30 Days, and so on. I then ordered the Backlog Timeframe on that 1,2,3 column and all the percentages change to 100%.
Any way to keep the % of CT and sort the way I need?
hi, @KMcCarthy9
If possible, could you share your sample pbix file for us have a test?
There should be something wrong in other.
Best Regards,
Lin
@v-lili6-msft wrote:hi, @KMcCarthy9
If possible, could you share your sample pbix file for us have a test?
There should be something wrong in other.
Best Regards,
Lin
Hi Lin, Beginner question here...how do I share a sample file with you? Thank you.
hi, @KMcCarthy9
Sample data and expected output would help tremendously.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
Hi @v-lili6-msft
Thank you so much for all the information. It is very appreciated.
Here is my sample file. It is a very stripped down, simplistic version, but still has the same issue.
https://1drv.ms/u/s!AhU3XQlTk1LVglGqGMmx55Qz-Y-g?e=WosJwS
NOTE: I was able to somewhat solve my problem by changing "Within 30 Days" to just "30 Days." By doing this I didn't have to manually sort and the %'s stayed the same. But I would like to know why when sorting it messed up the % of column totals.
Thank you!!
hi, @KMcCarthy9
1. For your requirement, you want "% of CT" in the matrix, you should use ALLSELECTED([Backlog Timeframe]) in formula like below:
% of Total Workorders = VAR _FilterCount = COUNTROWS('Backlog Trend Data') VAR _AllCount = CALCULATE(COUNTROWS('Backlog Trend Data'),ALLSELECTED('Backlog Trend Data'[Backlog Timeframe - All Time])) RETURN DIVIDE(_FilterCount,_AllCount)
and you said it gives you the result of 100% in each cell.
So there should be a dim Backlog Timeframe table, please replace ALLSELECTED(workorder[Backlog Timeframe]) with it.
2. In this sample pbix, I find that there is a logic error in your formula:
% of Total Workorder Color = VAR _30DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) = "Within 30 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False) VAR _30DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) = "Within 30 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False) VAR _31DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="31 - 60 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False) VAR _31DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="31 - 60 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False) VAR _61DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time])="61+ Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False) VAR _61DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="61+ Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False) RETURN SWITCH( TRUE(), _61DaysBL && [% of Total Workorders] <=.049, "#C6EFCE",--Green _61DaysBL && [% of Total Workorders] >= .050,"#FFC7CE",--Red _31DaysBL && [% of Total Workorders] <=.099, "#C6EFCE", _31DaysBL && [% of Total Workorders] >= .010,"#FFC7CE", _30DaysBL && [% of Total Workorders] >= .850,"#C6EFCE", _30DaysBL && [% of Total Workorders] <=.849, "#FFC7CE", _61DaysCO && [% of Total Workorders] <=.300, "#C6EFCE", _61DaysCO && [% of Total Workorders] >= .319,"#FFC7CE", _31DaysCO && [% of Total Workorders] <=.700, "#FFC7CE", _31DaysCO && [% of Total Workorders] >= .710,"#C6EFCEE", _30DaysCO && [% of Total Workorders] >= .710,"#C6EFCE", _30DaysCO && [% of Total Workorders] <=.700, "#FFC7CE", "#FFF000")--Yellow
As the red part, all the values [% of Total Workorders] are greater 0.10 and less than 0.99 will all show as red("#FFC7CE"). please adjust it.
For example:
If less than 0.4 is green or greater than 0.5 is red
% of Total Workorder Color = VAR _30DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) = "Within 30 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False) VAR _30DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) = "Within 30 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False) VAR _31DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="31 - 60 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False) VAR _31DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="31 - 60 Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False) VAR _61DaysBL = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time])="61+ Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Backlog"),True,False) VAR _61DaysCO = IF(AND(SELECTEDVALUE('Backlog Trend Data'[Backlog Timeframe - All Time]) ="61+ Days",SELECTEDVALUE('Backlog Trend Data'[Workorder Category])="Closeout"),True,False) RETURN SWITCH( TRUE(), _61DaysBL && [% of Total Workorders] <=.49, "#C6EFCE",--Green _61DaysBL && [% of Total Workorders] >= .50,"#FFC7CE",--Red _31DaysBL && [% of Total Workorders] <=.39, "#C6EFCE", _31DaysBL && [% of Total Workorders] >= .40,"#FFC7CE", _30DaysBL && [% of Total Workorders] >= .850,"#C6EFCE", _30DaysBL && [% of Total Workorders] <=.849, "#FFC7CE", _61DaysCO && [% of Total Workorders] <=.300, "#C6EFCE", _61DaysCO && [% of Total Workorders] >= .319,"#FFC7CE", _31DaysCO && [% of Total Workorders] <=.700, "#FFC7CE", _31DaysCO && [% of Total Workorders] >= .710,"#C6EFCEE", _30DaysCO && [% of Total Workorders] >= .710,"#C6EFCE", _30DaysCO && [% of Total Workorders] <=.700, "#FFC7CE", "#FFF000")--Yellow
Result:
Best Regards,
Lin
Hi @v-lili6-msft, this is extremely delayed and I apologize but wanted to thank you for taking the time to help me with this issue.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |