Running count with a filter

[Anonymous]

Hi all, 

Not sure if this is because it's a New Year and my brain has yet to accept it, or if it's actually something that's going to be difficult! 

I have the following:

RunningTot = 
CALCULATE(
    DISTINCTCOUNT('Location'[ID]),
    FILTER(
        ALLSELECTED('Location'[ID]),
        ISONORAFTER('Location'[ID], MIN('Location'[ID]), ASC)
    )
)

Works perfectly, counts the staff, tells me how many they are in each location

However, I now want to add in an additional complication.

On this table there is also a location field - which varies between office, field, home-based, flexible - and allows for multiple ticks for each staff member (as it's where they are willing to be). What I want to do is break down my above count, so that it only counts those that are ticked as 'flexible' but then reports back on the other locations as well. So, outcomes being out of everyone that is 'flexible' - 9 are home-based too, 14 are field, 2 are office - duplication of counts doesn't matter in this instance. 

I can't use a filter in the report as if I tick flexible, it will just show those that are ticked as that and disregard the other locations (which is what I want to report back on).

Hopefully this makes sense

Thanks for any guidance 

[amitchandak]

@Anonymous ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Check if Window function can help

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

https://youtu.be/cN8AO3_vmlY?t=31980

[Anonymous · Accepted solution]

@amitchandak Thanks for trying to help with this - however, I have woken up a bit now and made a custom column that allows 'flexible' to act as a seperate filter (instead of trying to do it in the same column).

All works now! Thanks again for trying to help, greatly appreciated. 

[Anonymous]

To give a quick example as I can't attach on my current slow connection.

1	office
1	field
2	home-based
3	home-based
3	field
4	home-based
4	field
4	flexible
5	flexible
5	home-based
5	office
5	field

So, the normal counts would be

Office - 2

Field - 4

Home-based - 4

Flexible - 2

But then we would filter on the flexible field so the count would be (just ids 4 & 5 are marked as flexible)

Office - 1

Field - 2

Home-based - 2

Flexible - 2