CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
idalsin
idalsin

Reputation: 546

PowerBI - Cumulative Total with Multiple Criteria

New to PowerBI, so forgive me for the description here. I'm working with a dataset of retail headcount sensors, which gives me a table of locations, timestamps, and a count of shoppers:

Room     TimeStamp     Count_In
123      3/13/2019     8
456      4/4/2019      9
123      3/28/2019     11
123      3/18/2019     11
456      3/22/2019     3
etc...

I'm trying to calculate a running total for each "room" over time. The overall running total column is easy:

C_In =
CALCULATE (
    SUM ( Sheet1[In] ),
    ALL ( Sheet1 ),
    Sheet1[Time] <= EARLIER ( Sheet1[Time] )
)

But I'm unable to figure out how to add that second filter, making sure that I'm only summing for each distinct location. Help is appreciated!

Upvotes: 0

Views: 2451

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Your ALL function removes all context on Sheet1, try using ALLEXCEPT to keep the row context of the Room.

C_In =
CALCULATE (
    SUM ( Sheet1[In] ),
    ALLEXCEPT ( Sheet1, Sheet1[Room] ),
    Sheet1[Time] <= EARLIER ( Sheet1[Time] )
)

Upvotes: 2

Related Questions