Solved: Re: Selective sum based on row type

roelf · ‎11-04-2022

***Sorry for the sloppy table format - seems like the forum changes it when I post...

Hi

I need to be able to sum values in a column, based on a TYPE attribute in the same row. However, I need to be able to "reset" my cumulative total every time a new "set" of values start.

My data looks like this:

DateTime	Type	PickWeight
2022/11/04 12:00:00	pick	1.023
2022/11/04 12:00:12	pick	1.002
2022/11/04 12:00:24	pick	1.023
2022/11/04 12:00:36	pick	1.042
2022/11/04 12:00:48	quality	1.023
2022/11/04 12:01:00	pick	1.452
2022/11/04 12:01:12	stack	1.425
2022/11/04 12:01:24	pick	1.236
2022/11/04 12:01:36	quality	1.024
2022/11/04 12:01:48	pick	1.024
2022/11/04 12:02:00	pick	1.023
2022/11/04 12:02:12	pick	1.025
2022/11/04 12:02:24	quality	1.124
2022/11/04 12:02:36	stack	1.023
2022/11/04 12:02:48	pick	1.235
2022/11/04 12:03:00	pick	1.302
2022/11/04 12:03:12	pick	1.223
2022/11/04 12:03:24	stack	1.023
2022/11/04 12:03:36	pick	1.231
2022/11/04 12:03:48	pick	1.023
2022/11/04 12:04:00	pick	1.243
2022/11/04 12:04:12	quality	1.245
2022/11/04 12:04:24	quality	1.245
2022/11/04 12:04:36	stack	1.021
2022/11/04 12:04:48	pick	1.021

I have sorted the data sequentially, and there are various "TYPES" in the dataset.

A "set" are all the data entries between two "stack" types. I need to then get the total stack weight, which is the sum of all the "PickWeight" values, between two "stack" types.

So the column for "StackWeight" is what i need.

DateTime	Type	PickWeight	StackWeight
2022/11/04 12:00:00	pick	1.023
2022/11/04 12:00:12	pick	1.002
2022/11/04 12:00:24	pick	1.023
2022/11/04 12:00:36	pick	1.042
2022/11/04 12:00:48	quality	1.023
2022/11/04 12:01:00	pick	1.452
2022/11/04 12:01:12	stack	1.425	5.542
2022/11/04 12:01:24	pick	1.236
2022/11/04 12:01:36	quality	1.024
2022/11/04 12:01:48	pick	1.024
2022/11/04 12:02:00	pick	1.023
2022/11/04 12:02:12	pick	1.025
2022/11/04 12:02:24	quality	1.124
2022/11/04 12:02:36	stack	1.023	3.072
2022/11/04 12:02:48	pick	1.235
2022/11/04 12:03:00	pick	1.302
2022/11/04 12:03:12	pick	1.223
2022/11/04 12:03:24	stack	1.023	3.76
2022/11/04 12:03:36	pick	1.231
2022/11/04 12:03:48	pick	1.023
2022/11/04 12:04:00	pick	1.243
2022/11/04 12:04:12	quality	1.245
2022/11/04 12:04:24	quality	1.245
2022/11/04 12:04:36	stack	1.021	3.497
2022/11/04 12:04:48	pick	1.021

If there is someone that can assist me it will be greatly appreciated.

Thanks !

Roelf

mangaus1111 · ‎11-04-2022

Hi @roelf ,

try this

Column = 
IF('Tabelle23'[Type] ="stack",
    SUMX(
        FILTER(
            'Tabelle23',
              'Tabelle23'[DateTime] <= EARLIER(Tabelle23[DateTime]) && 'Tabelle23'[Type] = "pick"
                        ),
                'Tabelle23'[PickWeight]
             )
)

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

View solution in original post

Anonymous · ‎11-06-2022

Hi @roelf ,

I have created a simple sample, please refer to it to see if it helps you.

Create 2 columns first.

stack = IF('Table'[Type]="stack",0,1)

minflagdate = 
calculate(MIN('Table'[DateTime]),FILTER(ALLSELECTED('Table'),'Table'[DateTime]>=EARLIER('Table'[DateTime])&&'Table'[stack]=0))

Then create a measure.

Measure = var _1= CALCULATE(SUM('Table'[PickWeight]),FILTER(ALL('Table'),'Table'[minflagdate]=SELECTEDVALUE('Table'[minflagdate])&&'Table'[Type]="pick"))
return
IF(MAX('Table'[Type])="stack",_1,BLANK())

Best Regards

Community Support Team _ Polly

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

View solution in original post

Anonymous · ‎11-06-2022

Hi @roelf ,

I have created a simple sample, please refer to it to see if it helps you.

Create 2 columns first.

stack = IF('Table'[Type]="stack",0,1)

minflagdate = 
calculate(MIN('Table'[DateTime]),FILTER(ALLSELECTED('Table'),'Table'[DateTime]>=EARLIER('Table'[DateTime])&&'Table'[stack]=0))

Then create a measure.

Measure = var _1= CALCULATE(SUM('Table'[PickWeight]),FILTER(ALL('Table'),'Table'[minflagdate]=SELECTEDVALUE('Table'[minflagdate])&&'Table'[Type]="pick"))
return
IF(MAX('Table'[Type])="stack",_1,BLANK())

Best Regards

Community Support Team _ Polly

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

roelf · ‎11-04-2022

Thanks for the reply @mangaus1111 .

That partially answers my scenario, but as you can see the "Stack Weights" cumulate, example the second stack weight is 9.85, where it should have been 4.308. So I'm wondering if there is a way to substract a stackweight from a previous one ?

mangaus1111 · ‎11-04-2022

Hi @roelf ,

try this

Column = 
IF('Tabelle23'[Type] ="stack",
    SUMX(
        FILTER(
            'Tabelle23',
              'Tabelle23'[DateTime] <= EARLIER(Tabelle23[DateTime]) && 'Tabelle23'[Type] = "pick"
                        ),
                'Tabelle23'[PickWeight]
             )
)

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