Cumulative per day per person

[roelf]

Hi

My dashboard is to measure the production of KG's packed, per person, per day in a factory.

My data looks like this:

Date	Person	Real Weight
01-Jan-22	Person A	1
01-Jan-22	Person A	1.5
01-Jan-22	Person A	1
01-Jan-22	Person A	1.6
01-Jan-22	Person A	1
01-Jan-22	Person B	3.5
01-Jan-22	Person B	3
01-Jan-22	Person B	1.5
01-Jan-22	Person B	2
02-Jan-22	Person A	1.5
02-Jan-22	Person A	1
02-Jan-22	Person A	1
02-Jan-22	Person A	0.5
02-Jan-22	Person B	1
02-Jan-22	Person B	2

I Need to get it as follows:

Date	Person	Real Weight	Cumulative Weight
01-Jan-22	Person A	1	1
01-Jan-22	Person A	1.5	2.5
01-Jan-22	Person A	1	3.5
01-Jan-22	Person A	1.6	5.1
01-Jan-22	Person A	1	6.1
01-Jan-22	Person B	3.5	3.5
01-Jan-22	Person B	3	6.5
01-Jan-22	Person B	1.5	8
01-Jan-22	Person B	2	10
02-Jan-22	Person A	1.5	1.5
02-Jan-22	Person A	1	2.5
02-Jan-22	Person A	1	3.5
02-Jan-22	Person A	0.5	4
02-Jan-22	Person B	1	1
02-Jan-22	Person B	2	3

I then have a futher requirement, where I need to know how much was below and how much over 4 KG.

So my table needs to expand to this

Date	Person	Real Weight	Cumulative Weight	Below 4	Above 4
01-Jan-22	Person A	1	1	1
01-Jan-22	Person A	1.5	2.5	1.5
01-Jan-22	Person A	1	3.5	1
01-Jan-22	Person A	1.6	5.1	0.5	1.1
01-Jan-22	Person A	1	6.1		1
01-Jan-22	Person B	3.5	3.5	3.5
01-Jan-22	Person B	3	6.5	0.5	2.5
01-Jan-22	Person B	1.5	8		1.5
01-Jan-22	Person B	2	10		2
02-Jan-22	Person A	1.5	1.5	1.5
02-Jan-22	Person A	1	2.5	1
02-Jan-22	Person A	1	3.5	1
02-Jan-22	Person A	0.5	4	0.5
02-Jan-22	Person B	1	1	1
02-Jan-22	Person B	2	3	2

I have tried about 5 different ways that I could see on the forum, so thanks to those who's answered a similar problem. I have however not found one that gives me the answer I require.

If any of you can assist, it will be much appreciated !

Thanks

Roelf

[roelf]

Thank you all for your suggestions - they all worked 100%

What I ended up using:

- Sorted the data in the Power Query

- Then added an index

- Then used the formula given to get the cumulative amount

I have also checked out other examples which suggesting using "RANKX" as a method instead of adding an index column - not sure which is better. I personally preferred adding an index in the query so that I am sure all rows have values.

Thanks again all - much appreciated !!

[v-yanjiang-msft · Accepted solution]

Hi @roelf ,

According to your description, here's my solution.

Add an index column in Power Query, then create three calculated columns.

Cumulative Weight =
SUMX (
    FILTER (
        'Table',
        'Table'[Date] = EARLIER ( 'Table'[Date] )
            && 'Table'[Person] = EARLIER ( 'Table'[Person] )
            && 'Table'[Index] <= EARLIER ( 'Table'[Index] )
    ),
    'Table'[Real Weight]
)

Above 4 =
VAR _Cum =
    MAXX (
        FILTER (
            'Table',
            'Table'[Date] = EARLIER ( 'Table'[Date] )
                && 'Table'[Person] = EARLIER ( 'Table'[Person] )
                && 'Table'[Cumulative Weight] > 4
                && 'Table'[Cumulative Weight] < EARLIER ( 'Table'[Cumulative Weight] )
        ),
        'Table'[Cumulative Weight]
    )
RETURN
    IF (
        [Cumulative Weight] > 4,
        [Cumulative Weight]
            - IF ( _Cum = BLANK (), 4, _Cum )
    )

Below 4 =
IF (
    [Cumulative Weight] < 4,
    'Table'[Real Weight],
    IF (
        'Table'[Real Weight] - 'Table'[Above 4] = 0,
        BLANK (),
        'Table'[Real Weight] - 'Table'[Above 4]
    )
)

Get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[truptis]

Hi @roelf ,

For cumulative weight try this:

Result= SUMX (

    FILTER (

        ALLSELECTED ( tablename[Date] ),

        Tablename[Date] <= MAX ( tablename[Date] )

    ),

    [Realweight]

)

@roelf -> please hit the thumbs up & mark it as a solution if it helps you. Thanks.

[amitchandak]

@roelf , Add an index column in power query  first and then try a new column

cumm  = sumx(filter(Table, [Person] = earlier([Person]) && [Index] <= earlier([index]) ), [Weight])