Forum Discussion

RyndaRaw's avatar
RyndaRaw
Icon for Helper I rankHelper I
6 years ago
Solved

Need a measure that will aggregate based on whether two types exist

Hi Everyone,   I need a measure that can give me the total column below.   Basically, I want to aggregate the Amount column only for ID's that have both Type A & Type B rows. If it has only Type ...
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi RyndaRaw ,

    Modify the formula like this:

    Result =
VAR A =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[ID#] ),
        'Table'[Type] = "Type A"
    )
VAR B =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[ID#] ),
        'Table'[Type] = "Type B"
    )
VAR tab =
    SUMMARIZE (
        'Table',
        'Table'[ID#],
        'Table'[Type],
        'Table'[Amount],
        "_New Amount", IF (
            'Table'[Amount] < 0
                && 'Table'[Type] = "Type B",
            ABS ( 'Table'[Amount] ),
            'Table'[Amount]
        )
    )
VAR total =
    SUMX ( FILTER ( tab, [ID#] = EARLIER ( 'Table'[ID#] ) ), [_New Amount] )
RETURN
    IF (
        A > 0,
        IF (
            B > 0,
            IF ( 'Table'[Type] = "Type B", total ),
            CALCULATE ( SUM ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[ID#] ) )
        )
    )

     

    Best Regards,
    Yingjie Li

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

RyndaRaw , Try a new column like

new column
 var _A = sumx(filter(Table,[ID] =earlier[ID] && [Type] ="A"),[Amount])
 var _B = sumx(filter(Table,[ID] =earlier[ID] && [Type] ="B"),[Amount])
 return 
 if(isblank(_A) , blank(), _A+_B)

 

RyndaRaw's avatar
RyndaRaw
Icon for Helper I rankHelper I
6 years ago

This solution almost works. for some reason, it's adding the abs value of both types rather than the value. So if Type B is a negative value, it treats it as postive.

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi RyndaRaw ,

    Modify the formula like this:

    Result =
VAR A =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[ID#] ),
        'Table'[Type] = "Type A"
    )
VAR B =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[ID#] ),
        'Table'[Type] = "Type B"
    )
VAR tab =
    SUMMARIZE (
        'Table',
        'Table'[ID#],
        'Table'[Type],
        'Table'[Amount],
        "_New Amount", IF (
            'Table'[Amount] < 0
                && 'Table'[Type] = "Type B",
            ABS ( 'Table'[Amount] ),
            'Table'[Amount]
        )
    )
VAR total =
    SUMX ( FILTER ( tab, [ID#] = EARLIER ( 'Table'[ID#] ) ), [_New Amount] )
RETURN
    IF (
        A > 0,
        IF (
            B > 0,
            IF ( 'Table'[Type] = "Type B", total ),
            CALCULATE ( SUM ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[ID#] ) )
        )
    )

     

    Best Regards,
    Yingjie Li

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