cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
accortdr Frequent Visitor
Frequent Visitor

Switch function to get dynamic look at last month's productivity

I have created a slicer that allows the user to switch between 4 departments and filter the visuals accordingly. I am trying to apply this switch to a productivity calculation for each department. "Last_Month" is a measure that will be displayed on a card, but with the code posted below it is displaying 0 as the result. Does anyone have any suggestions as to how to fix this?

 

Last_Month = Switch(
SELECTEDVALUE(MeasureSwitch[Department]),
"Department1", Divide(Sum('Department1'[ThroughputD1]),Calculate(
Sum(Daily_Hours_Clean[Hours_worked]),
FILTER(Daily_Hours_Clean,[Department] = "Department1"),PREVIOUSMONTH('Date'[Date])),0),

"Department2", Divide(Sum('Department2'[ThroughputD2]),Calculate(
Sum(Daily_Hours_Clean[Hours_worked]),
FILTER(Daily_Hours_Clean,[Department] = "Department2"),PREVIOUSMONTH('Date'[Date])),0),

"Department3", Divide(Sum('Department3'[ThroughputD3]),Calculate(
Sum(Daily_Hours_Clean[Hours_worked]),
FILTER(Daily_Hours_Clean, [Department] = "Department3"),PREVIOUSMONTH('Date'[Date])),0),

"Department4", Divide(Sum('Stain Log'[ThroughputD4]),Calculate(
Sum(Daily_Hours_Clean[Hours_worked]),
FILTER(Daily_Hours_Clean,[Department] = "Department4"),PREVIOUSMONTH('Date'[Date])),0))
1 REPLY 1
Community Support Team
Community Support Team

Re: Switch function to get dynamic look at last month's productivity

Hi @accortdr 

Please show me the relationship among these tables.

"Department1", "MeasureSwitch", "Daily_Hours_Clean", "Date"

 

Try the single formula in your table to see if it works

eg.

measure1=

Divide(Sum('Department1'[ThroughputD1]),Calculate(
Sum(Daily_Hours_Clean[Hours_worked]),
FILTER(Daily_Hours_Clean,[Department] = "Department1"),PREVIOUSMONTH('Date'[Date])),0)
 
meausre2=
Switch(
SELECTEDVALUE(MeasureSwitch[Department]),
"Department1", 1,
"Department2", 2,
"Department3", 3,
"Department4", 4)
 
It is better to share a simple data for me to test.
eg.
table1,2,3,4
columns in tables (only columns used in this measure needed to be shown)
relationships among these tables.
 
Best Regards
Maggie