cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FlowViz
Helper III
Helper III

Measure filtered by slicer - group results by column value

Hi all,

 

I have a measure within my report which calculates the percentage of time items spent in 'active' states (as determined by what the user selects in the slicer). This gives a flow efficiency percentage Active Time / (Active Time + Wait Time).

 

I'm struggling with the measure for this for each individual WorkItemID, as it is giving the same answer for all, for example for 725:

 

Screenshot 2022-02-21 at 17.00.23.png

 

It should actually be showing 3%, as that was the amount of time spent in 'active' states - 4 / (4 + 38 + 63 + 40) * 100

 

Screenshot 2022-02-21 at 17.00.57.png

 

Anyone have any ideas as to hwo to fix this? Pbix is available here if it helps...

1 ACCEPTED SOLUTION

@FlowViz I think the ALLSELECTED is removing more filter context than intended (plus you should never use that function inside of an iterator).

 

Try something simpler like this as a starting point:

FE =
VAR _active =
    CALCULATE (
        [Time in column (days)],
        FILTER (
            VALUES ( 'WorkItems FlowEfficiency'[Column] ),
            'WorkItems FlowEfficiency'[Column]
                IN VALUES ( slicer[WorkItems FlowEfficiency_Column] )
        )
    )
VAR _all = [Time in column (days)]
RETURN
    _active / _all

View solution in original post

5 REPLIES 5
KNP
Super User
Super User

I had a look at the PBIX file. I don't have time right now to come up with a solution unfortunately, but I'm sure someone will have something for you shortly.

 

I like what you did with the navigation features you've built into the report. Those bookmarks must have taken forever. Especially the light/dark mode. I posted an idea a while back on the ideas site that you might like to vote for.

https://ideas.powerbi.com/ideas/idea/?ideaid=781638b4-9b86-eb11-8ced-281878e6452b

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Thank you - yes it did take a while! Have upvoted your idea. Hopefully someone can help me out...

@FlowViz - I may have have put others off answering with my initial relply.

 

If (@BA_Pete@AlexisOlson@parry2k) you have time to look at this issue, the below is the measure in question in the PBIX file linked in the original question.

FE =
VAR _active =
    SUMX (
        FILTER (
            ALLSELECTED ( 'WorkItems FlowEfficiency' ),
            'WorkItems FlowEfficiency'[Column]
                IN VALUES ( slicer[WorkItems FlowEfficiency_Column] )
        ),
        'WorkItems FlowEfficiency'[Time in column (days)]
    )
VAR _all =
    SUMX (
        ALLSELECTED ( 'WorkItems FlowEfficiency' ),
        'WorkItems FlowEfficiency'[Time in column (days)]
    )
RETURN
    _active / _all

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

@FlowViz I think the ALLSELECTED is removing more filter context than intended (plus you should never use that function inside of an iterator).

 

Try something simpler like this as a starting point:

FE =
VAR _active =
    CALCULATE (
        [Time in column (days)],
        FILTER (
            VALUES ( 'WorkItems FlowEfficiency'[Column] ),
            'WorkItems FlowEfficiency'[Column]
                IN VALUES ( slicer[WorkItems FlowEfficiency_Column] )
        )
    )
VAR _all = [Time in column (days)]
RETURN
    _active / _all

EDIT: thank you again, this is working just as I want it to 🙂

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors