cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Etienne123
Advocate II
Advocate II

Cumulative Sum, reset by recurring group

I need to calculate the time an animal spends in a sector, and only for the time it resides in that sector - an animal can move in and out of a sector many times. Each "visit" constitutes a new visit and the reset must apply. In the data below, you can see how animal 100A visited sector A, then moved to sector B, then moved back to sector A.

 

RodentId : Cage : Sector : Time : Seconds : Index

100A: XX: A: 15:20:21: 1 second: 1
100A: XX: A: 15:20:22: 1 second: 2
100A: XX: B: 15:20:23: 1 second: 3
100A: XX: B: 15:20:24: 1 second: 4
100A: XX: B: 15:20:25: 1 second: 5
100A: XX: A: 15:20:26: 1 second: 6
100A: XX: A: 15:20:27: 1 second: 7

 

Below is my current calculation, and the fact that sector is recurring is causing the "revisit" to sector A, not to reset the calculation and continues from the previous visit. How do I overcome this?

 

GroupPeriod =
SUMX (
    FILTER (
        'Sample data 1',
        EARLIER ( 'Sample data 1'[rodentId]) = 'Sample data 1'[rodentId]
     && EARLIER ( 'Sample data 1'[cage]) = 'Sample data 1'[cage]
     && EARLIER ( 'Sample data 1'[Sector] ) = 'Sample data 1'[Sector] &&
     && EARLIER ( 'Sample data 1'[Index] ) >= 'Sample data 1'[Index]
    ),
    'Sample data 1'[Period]
)

1 ACCEPTED SOLUTION

Issue resolved for anyone interested. The issue was with the formula GroupMinTime. It required a statement to enforce a reset on RodentId.

 

GroupMinTime =
CALCULATE(
MAX(
'Sample data 1'[GroupVar])
,FILTER(all('Sample data 1')
,'Sample data 1'[Index] <= EARLIER('Sample data 1'[Index])
&& 'Sample data 1'[RodentId] = EARLIER('Sample data 1'[RodentId])
))

View solution in original post

5 REPLIES 5
v-yuezhe-msft
Microsoft
Microsoft

@Etienne123,

Create the following columns in your table.

Group = MINX(FILTER(ALL('Sample data 1'),'Sample data 1'[Sector]<>EARLIER('Sample data 1'[Sector])&&'Sample data 1'[Time]>EARLIER('Sample data 1'[Time])),'Sample data 1'[Time])
GroupPeriod = 
SUMX (
    FILTER (
        'Sample data 1',
        EARLIER ( 'Sample data 1'[rodentId]) = 'Sample data 1'[rodentId]
     && EARLIER ( 'Sample data 1'[cage]) = 'Sample data 1'[cage]
     && EARLIER ( 'Sample data 1'[Sector] ) = 'Sample data 1'[Sector] 
&& EARLIER('Sample data 1'[Group])='Sample data 1'[Group]
     && EARLIER ( 'Sample data 1'[Index] ) >= 'Sample data 1'[Index]
    ),
    'Sample data 1'[Seconds]
)

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi

 

I cannot get your suggestion to work. Note that in my dataset period constiture what you termed as "seconds" as it shows each 0.5 interval for each record.

 

Group =
MINX(
FILTER(
ALL('Sample data 1'),
'Sample data 1'[Sector]<>EARLIER('Sample data 1'[Sector])
&&'Sample data 1'[Time]>EARLIER('Sample data 1'[Time]))
,'Sample data 1'[Time])

 

And then

 

GroupPeriod =
SUMX (
FILTER (
'Sample data 1',
EARLIER ( 'Sample data 1'[rodentId]) = 'Sample data 1'[rodentId]
&& EARLIER ( 'Sample data 1'[cage]) = 'Sample data 1'[cage]
&& EARLIER ( 'Sample data 1'[Sector] ) = 'Sample data 1'[Sector]
&& EARLIER('Sample data 1'[Group]) = 'Sample data 1'[Group]
&& EARLIER ( 'Sample data 1'[Index] ) >= 'Sample data 1'[Index]
), 'Sample data 1'[Period]
)

 

Example

 

The Group formula do not group the cohort of visits to a sector together. 

@Etienne123,

Please share the complete table that contains period column and post expected result here.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Let me try and explain. This is a visual solutio tied to IoT sensor measures in a cage measurting the movement of animals. Imagine a cage, with 3 animals, moving freely between 6 sectors in a cage. Each sector has an IoT device that takes a measure every 0.5 seconds. The business outcome is to measure movement and how long an animal spends in any one sector. The data literally looks like the images below (I am trying to calculate GroupPeriod - it cumulatively sum each 0.5 second measure until there is a change in Cage, Rodent (Animal), and Sector when it should revert back to 0.5 and start cumulatively summing again).

 

I have some formulae that works somewhat, but there is still something wrong as it does not work 100% as it does not revert back to 0.5 in all instances:

 

Image 1 - you can see that the same rodent, changed from sector 3 to 2 and the GroupPeriod correctly reverted back to 0.5

Image 2 - you can see that the same rodent, changed back from 2 to 3, but GroupPeriod continue the cumulatifve sum from the previous change (it hould have reverted back to 0.5, but continues with 21.5 seconds

Image 3 - you can see here how there is a change in rodent and so the GroupPeriod correctly reverted back to 0.5 seconds

 

 

Current formulae below the images:

 

Image 1Image 1Image 2Image 2Image 3Image 3

Formulae:

 

GroupVar - creates a startying row for each series (I do not think this works correctly, but I cant figure out why)

 

GroupVar = VAR S1 = 'Sample data 1'[Sector] VAR S2 = CALCULATE(MIN('Sample data 1'[Sector]),FILTER('Sample data 1','Sample data 1'[Index] = EARLIER('Sample data 1'[Index])-1)) VAR S3 = if(S1<>S2,'Sample data 1'[Time],BLANK()) RETURN S3

 

GroupMinTime - fills the above valyue down within the series

 

GroupMinTime = CALCULATE(MAX('Sample data 1'[GroupVar]),FILTER(all('Sample data 1'),'Sample data 1'[Index] <= EARLIER('Sample data 1'[Index])))

 

Group - Creates a proper unique group to include Rodebnt, Sector, Cage and the above

 

Group = 'Sample data 1'[GroupMinTime] & 'Sample data 1'[rodentId] & 'Sample data 1'[Sector] & 'Sample data 1'[cage]

 

GroupPeriod - creates the cumulative sum

 

GroupPeriod =
SUMX (
FILTER (
'Sample data 1',
EARLIER ( 'Sample data 1'[rodentId]) = 'Sample data 1'[rodentId]
&& EARLIER ( 'Sample data 1'[cage]) = 'Sample data 1'[cage]
&& EARLIER ( 'Sample data 1'[Sector] ) = 'Sample data 1'[Sector]
&& EARLIER('Sample data 1'[Group]) = 'Sample data 1'[Group]
&& EARLIER ( 'Sample data 1'[Index] ) >= 'Sample data 1'[Index]
), 'Sample data 1'[Period]
)

 

Any ideas greatly appreciated

Issue resolved for anyone interested. The issue was with the formula GroupMinTime. It required a statement to enforce a reset on RodentId.

 

GroupMinTime =
CALCULATE(
MAX(
'Sample data 1'[GroupVar])
,FILTER(all('Sample data 1')
,'Sample data 1'[Index] <= EARLIER('Sample data 1'[Index])
&& 'Sample data 1'[RodentId] = EARLIER('Sample data 1'[RodentId])
))

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!