Forum Discussion
Conditional formatting when the measure used for formatting contains WEEKNUM
Hello,
I have an issue with conditional formatting when using two measures. I want to format the values of my table (where the coloumns are the last 10 weeks) by using the value of a measure.
Values of the table:
ClaimCount = Sum(Claims)
First measure that i use as a 'target' value is the average of the last 10 weeks:
Avg 10w. claims =
var _weeks10=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-10 )
var _weeks9=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-9 )
var _weeks8=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-8 )
var _weeks7=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-7 )
var _weeks6=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-6 )
var _weeks5=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-5 )
var _weeks4=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-4 )
var _weeks3=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-3 )
var _weeks2=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2)-2 )
var _weeks1=CALCULATE( [ClaimCount],Dim_date[WeekNo]=WEEKNUM(TODAY(),2) -1 )
return
if(
(_weeks1+_weeks2+_weeks3+_weeks4+_weeks5+_weeks6+_weeks7+_weeks8+_weeks9+_weeks10)/10=blank(),
" ",
(_weeks1+_weeks2+_weeks3+_weeks4+_weeks5+_weeks6+_weeks7+_weeks8+_weeks9+_weeks10)/10
)
Then i use this conditional formatting code for the color:
Color =
switch (TRUE(),
[Avg 10w. claims]
>= [ClaimCount]
,
"#D64550",
"#089000"
)
The problem is that it doesnt work, it only shows the hex color for green (#089000), even though some of the values are under the target value and should show red (#D64550).
I have dobble checked, and the Avg. 10 weeks measure shows the correct number on a card (and it is a number). What have i done wrong?
Anonymous
I messed with it a bit and your format measure should work. I did do a different Avg 10w measure:
Avg 10w. claims = VAR _EOPW = CALCULATE ( MAX ( Dim_date[Date] ), Dim_date[WeekNo] < WEEKNUM ( TODAY (), 2 ) ) RETURN CALCULATE ( AVERAGEX ( VALUES ( Dim_date[WeekNo] ), [ClaimCount] ), DATESBETWEEN ( Dim_date[Date], _EOPW - 69, _EOPW ) )Take a look at my attached sample, see if that helps. I used your color measure as is to format my ClaimCount field:
9 Replies
- amitchandakSuper User
Anonymous , for rolling week refer these
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123Hope you have used "field" option in conditional formatting?
https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values- AnonymousNot applicable
Yes, i have used the field option.
- jdbuchanan71Super User
Anonymous
I messed with it a bit and your format measure should work. I did do a different Avg 10w measure:
Avg 10w. claims = VAR _EOPW = CALCULATE ( MAX ( Dim_date[Date] ), Dim_date[WeekNo] < WEEKNUM ( TODAY (), 2 ) ) RETURN CALCULATE ( AVERAGEX ( VALUES ( Dim_date[WeekNo] ), [ClaimCount] ), DATESBETWEEN ( Dim_date[Date], _EOPW - 69, _EOPW ) )Take a look at my attached sample, see if that helps. I used your color measure as is to format my ClaimCount field:
- v-xicaiCommunity Support
Hi Anonymous ,
You may change your formula like DAX below.
Color = VAR ClaimCount = SUM ( [Claims] ) VAR _weeks10 = CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 10 ) VAR _weeks9 = CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 9 ) VAR _weeks8 = CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 8 ) VAR _weeks7 = CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 7 ) VAR _weeks6 = CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 6 ) VAR _weeks5 = CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 5 ) VAR _weeks4 = CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 4 ) VAR _weeks3 = CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 3 ) VAR _weeks2 = CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 2 ) VAR _weeks1 = CALCULATE ( SUM ( [Claims] ), Dim_date[WeekNo] = WEEKNUM ( TODAY (), 2 ) - 1 ) VAR Avg10wclaims = DIVIDE ( _weeks1 + _weeks2 + _weeks3 + _weeks4 + _weeks5 + _weeks6 + _weeks7 + _weeks8 + _weeks9 + _weeks10, 10 ) RETURN IF ( Avg10wclaims >= ClaimCount, "#D64550", "#089000" )Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.