Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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
Thank you! This is amazing 🙂
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
Bumping this to see if anyone had ideas?
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...
Hi @FlowViz ,
How do you define whether the column is 'active' or 'waiting'? By [StateCategory] column?
Best Regards,
Jay
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
79 | |
61 | |
60 | |
58 |
User | Count |
---|---|
151 | |
113 | |
99 | |
80 | |
72 |