Forum Discussion
EOW
Advocate II
Advocate IIHow to use a calculation group as a column switcher for multiple column groups?
I've got a report tracking yields at different steps in production, formatted in the following manner:
| Lot | Product | Finish Flag | Stp1 Yield | Stp1 Target Static | Stp1 Target Live | Stp1 Target File | Stp2 Yield | Stp2 Target Static | Stp2 Target Live | Stp2 Target File |
| A101 | W1 | Y | 98% | 96% | 95% | 95% | 95% | 92% | 90% | 92% |
| A102 | W1 | Y | 97% | 96% | 95% | 95% | 91% | 92% | 90% | 92% |
| A103 | W2 | Y | 90% | 89% | 87% | 88% | 82% | 84% | 82% | 83% |
| A104 | W2 | N | 87% | 89% | 87% | 88% | 80% | 84% | 82% | 83% |
I want to slice between Static, Live and File Targets in my measures, Is there a way to leverage a calulcation group and simplify the measures I've written. I've put the target set I want to use in a table called 'Target Use'.
Stp1 Target =
var FilterRows = FILTER('Data', 'Data'[Finish Flag] = "Y")
var TargUse = SELECTEDVALUE('Target Use'[TargetToUse])
return
SWITCH(TRUE()
, TargUse = "Static Targets" , AVERAGEX(FilterRows, Data[Stp1 Target Static])
, TargUse = "Live Targets" , AVERAGEX(FilterRows, Data[Stp1 Target Live])
, TargUse = "File Targets" , AVERAGEX(FilterRows, Data[Stp1 Target File])
, AVERAGEX(FilterRows, Data[Stp1 Target Static])
)
Stp2 Target =
var FilterRows = FILTER('Data', 'Data'[Finish Flag] = "Y")
var TargUse = SELECTEDVALUE('Target Use'[TargetToUse])
return
SWITCH(TRUE()
, TargUse = "Static Targets" , AVERAGEX(FilterRows, Data[Stp2 Target Static])
, TargUse = "Live Targets" , AVERAGEX(FilterRows, Data[Stp2 Target Live])
, TargUse = "File Targets" , AVERAGEX(FilterRows, Data[Stp2 Target File])
, AVERAGEX(FilterRows, Data[Stp2 Target Static])
)
Is there a way to use calculation groups to generalise the measures?
1 Reply
unpivot the target columns may help you.
