Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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