Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Cumulative Sum, reset by recurring group

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Cumulative Sum, reset by recurring group

09-27-2017
11:30 PM

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]

)

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2017
08:46 PM

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])

))

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-29-2017
03:07 AM

@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] )

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.*

If this post

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-30-2017
03:33 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-02-2017
12:31 AM

@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.*

If this post

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2017
04:08 PM

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:

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-04-2017
08:46 PM

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])

))