Use slicer to change measure

[FlowViz]

Hi folks,

I have a report page like so:

I want to calculate the percentage of time the WorkItemId (an item) was spent in an 'active' column vs. 'active' + 'waiting' column.
So for this example item (725), the only 'Active' column is 'In Test', therefore it should be:

4 / (0 + 38 + 40 + 63 + 4) = 3%

However if I chose a different item (696):

the 'Active' columns are 'In Build' / 'In Test' / 'In Progress' so this would be

(5 + 63) / (129 + 38 + 5 + 63) = 29%

I want to be able to use the Column slicer to control the calculation, as users will need to select their 'waiting' columns...

Can this be done? Pbix file is available here

[FlowViz]

Thank you! This is amazing 🙂

[v-jayw-msft · Accepted solution]

Hi @FlowViz ,

Check the steps.

1# create a slicer table and use it as slicer.

slicer = DISTINCT('WorkItems FlowEfficiency'[Column])

2# create a measure as below.

Measure 2 = 
var _active = SUMX(FILTER(ALLSELECTED('WorkItems FlowEfficiency'),'WorkItems FlowEfficiency'[Column] in VALUES(slicer[Column])),'WorkItems FlowEfficiency'[Measure])
var _all = SUMX(ALLSELECTED('WorkItems FlowEfficiency'),'WorkItems FlowEfficiency'[Measure])
return
_active/_all

Result:

Best Regards,

Jay

[FlowViz]

Bumping this to see if anyone had ideas?

[FlowViz]

I wanted the user to do this via the slicer (which is the "Column" column in the dataset), so if I want the user to select any "waiting" values and the formula to dynamically update based on selection...

[v-jayw-msft]

Hi @FlowViz ,

How do you define whether the column is 'active' or 'waiting'? By [StateCategory] column?

Best Regards,

Jay