How to get summary of consecutive non zero values in a measure?

[Anonymous]

I have a scenario where I need to get summary of values till it encounters zero and reset the summary to zero once it has the zero value.

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1.Create calculated column.

Column1 =
IF(
   'Table'[YELLOW]=0,1,0)

Column2 =
SUMX(
    FILTER(ALL('Table'),
    'Table'[FW_ID]<=EARLIER('Table'[FW_ID])),[Column1])

2. Create measure.

Test1 =
IF(
    MAX('Table'[Column1])=1,BLANK(),
COUNTX(
    FILTER(ALLSELECTED('Table'),
    'Table'[Column2]=MAX('Table'[Column2])&&[Column1]<>1),[FW_ID]))

Test2 =
SUMX(
    FILTER(ALL('Table'),[Test1]>1),[YELLOW])

3. Result:

Best Regards,

Liu Yang

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

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for cumulative sum grouped by non-consecutive-zero.

expected result measure: = 
VAR _yellow =
    ADDCOLUMNS ( ALL ( data ), "@yellow", [yellow:] )
VAR _condition =
    ADDCOLUMNS ( _yellow, "@condition", IF ( [@yellow] = 0, 1, 0 ) )
VAR _group =
    ADDCOLUMNS (
        _condition,
        "@group",
            SUMX (
                FILTER ( _condition, data[fw_id] <= EARLIER ( data[fw_id] ) ),
                [@condition]
            )
    )
VAR _currentrowgroup =
    MAXX ( FILTER ( _group, data[fw_id] = MAX ( data[fw_id] ) ), [@group] )
RETURN
    SUMX (
        FILTER (
            _group,
            [@group] = _currentrowgroup
                && data[fw_id] <= MAX ( data[fw_id] )
        ),
        [@yellow]
    )

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1.Create calculated column.

Column1 =
IF(
   'Table'[YELLOW]=0,1,0)

Column2 =
SUMX(
    FILTER(ALL('Table'),
    'Table'[FW_ID]<=EARLIER('Table'[FW_ID])),[Column1])

2. Create measure.

Test1 =
IF(
    MAX('Table'[Column1])=1,BLANK(),
COUNTX(
    FILTER(ALLSELECTED('Table'),
    'Table'[Column2]=MAX('Table'[Column2])&&[Column1]<>1),[FW_ID]))

Test2 =
SUMX(
    FILTER(ALL('Table'),[Test1]>1),[YELLOW])

3. Result:

Best Regards,

Liu Yang

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