Conditional Formatting Matrix with date Heirarchy.

[SACooper]

I have a matrix with a date heirarchy (Date, Week Commencing, Month and Year) in columns and then Manager and Employee's in rows, I also have a count of completed work items as values. So by drilling up and down or expanding/collpasing a manager we can easily see number of work items completed per employee/Manager/everyone per day/week/month/year -- so far so good.

There is a target of 43 items per day per employee - what I'd like to do is conditionally format the work item count so that if we're looking at dates its against a target of 43 items per day per employee, if its per week its 43 x 5 per employee and so on, and if we're looking at managers its 43 items per employee per day etc.

[SACooper]

... don't know why I hadn't thought of this as I use something similar elsewhere ... thank you so much!

[amitchandak · Accepted solution]

@SACooper , You can create a measure like

Switch(True(),

isinscope(Date[Date]) , 43 ,

isinscope(Date[week]) , 43*5 ,

isinscope(Date[month]) , 43*22

)

or

Sumx(employee,calculate(

Switch(True(),

isinscope(Date[Date]) , 43 ,

isinscope(Date[week]) , 43*5 ,

isinscope(Date[month]) , 43*22

)) )