Forum Discussion
Cumulative Sum, reset by recurring group
- 8 years ago
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])
))
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]
)

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
- Etienne1238 years ago
Advocate II
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 2Image 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
- Etienne1238 years ago
Advocate II
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])
))