Calculate total YTD within filtered table

[webportal]

This is MyTable:

It is defined in a variable within a calculated table, like:

Table=
VAR Tab = FILTER(...)
RETURN
Tab

I want a measure with the YTD values sum of Value, but ONLY for the ID's whose annual sum is negative.

So, the total sum of 123 = 33.

The total sum of 456 = 129

And the total sum of 789 = -205

So, I should get the following measure:

Measure =
VAR Tab = FILTER(...)

RETURN

TOTALYTD(....)

[MFelix · Accepted solution]

Hi  @webportal ,

Try the following code:

Measure
    =
    VAR temp_table =
        CALCULATETABLE (
            FILTER (
                SUMMARIZE (
                    ALL ( 'Table'[ID] ),
                    'Table'[ID],
                    "Total_Value", SUM ( 'Table'[Value] )
                ),
                [Total_Value] < 0
            ),
            ALL ( 'Table' )
        )
    VAR IDSELECTION =
        SELECTCOLUMNS ( temp_table, "ID", 'Table'[ID] )
    RETURN
        CALCULATE (
            TOTALYTD ( SUM ( 'Table'[Value] ), 'Table'[Date] ),
            FILTER ( ALL ( 'Table'[ID] ), 'Table'[ID] IN IDSELECTION )
        )

Result below and in attach PBIX file:

[v-yiruan-msft · Accepted solution]

Hi @webportal ,

You can create a measure as below:

Measure = 
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[ID],
        "svalue", CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
    )
VAR _tvalue =
    MAXX ( FILTER ( _tab, [svalue] < 0 ), [ID] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ID] = _tvalue
                && 'Table'[Date] <= SELECTEDVALUE ( 'Table'[Date] )
        )
    )

Best Regards

[MFelix · Accepted solution]

Hi  @webportal ,

Try the following code:

Measure
    =
    VAR temp_table =
        CALCULATETABLE (
            FILTER (
                SUMMARIZE (
                    ALL ( 'Table'[ID] ),
                    'Table'[ID],
                    "Total_Value", SUM ( 'Table'[Value] )
                ),
                [Total_Value] < 0
            ),
            ALL ( 'Table' )
        )
    VAR IDSELECTION =
        SELECTCOLUMNS ( temp_table, "ID", 'Table'[ID] )
    RETURN
        CALCULATE (
            TOTALYTD ( SUM ( 'Table'[Value] ), 'Table'[Date] ),
            FILTER ( ALL ( 'Table'[ID] ), 'Table'[ID] IN IDSELECTION )
        )

Result below and in attach PBIX file: