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.
Hi Guys.
I am trying to work out conditional fomatting for the following scenario. I have targets & actuals table, and also a date table. I have two measures, sum of targets and sum of actuals for each state. Now suppose if 50% of the quarter has elapsed, the state that has achieved 100% of targets based on 50% Quarter Elapsed to show green, achieved 90% of targets based on 50% Quarter Elapsed to show yellow, and anything below that as red. In the example below, as of 50% of quarter elapsed, QLD is green, NSW achieved 90% of quarter elapsed as yellow and VIC as red:
Solved! Go to Solution.
Hi, @Anonymous
Thank you for your feedback.
First, to create a calculate table which records the date for the current full year:
Table 2 = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))
Create a calculated column to represent the quarter of the current date:
Current_quarter = QUARTER('Table 2'[Date])
You also need a measure to calculate the percentage of the current quarter that has passed
_%of quarter(dynamically) =
var days_of_quarter=CALCULATE(COUNTROWS('Table 2'),FILTER('Table 2',[Current_quarter]=QUARTER(TODAY())))
var Today_elapsed=CALCULATE(COUNTROWS('Table 2'),FILTER('Table 2',[Current_quarter]=QUARTER(TODAY())&&[Date]<=TODAY()))
return DIVIDE(Today_elapsed,days_of_quarter)
I‘d like to note you that please replace % of the quarter with _%of quarter(dynamically),
Then you can get different results dynamically according to the current date
Please check my attachment sample, that's all I can do to help
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Thank you for your feedback, I'm sorry I didn't give you an accurate answer
please don't worry about that, We can choose fields that need to apply conditional format.
In this case, let's right-click the % achieved column and select the conditional formatting
Then select the background color and you could get the desired result based on the flag color field
Please check my attachment sample
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-angzheng-msft this is great. Exactly to what I was looking for. Now just one last mystery to be solved, how would you calculate the rolling % of QTD passed. I've noticed that you have used a hard coded value: % of quarter = 0.50
Is there a way to have this value change dynamically on daily basis? based on the number of days passed in the quarter
Hi, @Anonymous
Thank you for your feedback.
First, to create a calculate table which records the date for the current full year:
Table 2 = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))
Create a calculated column to represent the quarter of the current date:
Current_quarter = QUARTER('Table 2'[Date])
You also need a measure to calculate the percentage of the current quarter that has passed
_%of quarter(dynamically) =
var days_of_quarter=CALCULATE(COUNTROWS('Table 2'),FILTER('Table 2',[Current_quarter]=QUARTER(TODAY())))
var Today_elapsed=CALCULATE(COUNTROWS('Table 2'),FILTER('Table 2',[Current_quarter]=QUARTER(TODAY())&&[Date]<=TODAY()))
return DIVIDE(Today_elapsed,days_of_quarter)
I‘d like to note you that please replace % of the quarter with _%of quarter(dynamically),
Then you can get different results dynamically according to the current date
Please check my attachment sample, that's all I can do to help
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for your help. Your tricks helped out solve this problem of mine. Thank you so much buddy.
Hi, @Anonymous
Try to create measures below:
% of the quarter = 0.50
%Achieved total =
VAR StageGoal =
SUM ( 'Table'[Target] ) * 'Table'[% of the quarter]
RETURN
CALCULATE ( DIVIDE ( SUM ( 'Table'[Actual] ), StageGoal ) )
Flag color =
SWITCH (
TRUE (),
'Table'[%Achieved total] >= 1.00, "Green",
'Table'[%Achieved total] >= 0.90, "Yellow",
"Red"
)
Sample:
Result:
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks for the reply. I believe we are getting close with this. This is exactly what I was after, however, I would like the colours in the same column as %achieved, instead of a separate column of Flag Color.
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |