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 B, I want to ignore that value completely. If it has only Type A, I want that value to show in the total.

 

To SUM UP, if the ID has:
Type A & B - sum up both amounts

Type A only - show the amount

Type B only - show blank

 

ID #TypeAmountTotal (need measure)
7917Type A $  146,836.37 
7917Type B $  (67,970.21) $    78,866.16
12153Type A $      8,290.23 
12153Type B $    (1,770.00) $      6,520.23
11071Type A $      4,240.00 $      4,240.00
16442Type B $    (3,557.83) 
11534Type B $    (3,765.59) 
7931Type A $    (4,240.00) $    (4,240.00)
7916Type A $    (4,461.49) $    (4,461.49)
16448Type B $    (5,420.42) 
16305Type A $    (7,281.84) $    (7,281.84)
16305Tybe B $      8,290.23 $      1,008.39
16302Type A $      4,240.00 
16302Tybe B $      1,284.00 $      5,524.00
7942Type A $  256,916.23 
11984Type B $  (12,521.40) $  244,394.83

 

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.

6 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Please try this expression for your measure (I called the table with your example data 'AB'): 

     

    Total A or AB =
    VAR Arows =
    CALCULATE (
    COUNTROWS ( AB ),
    ALL ( AB ),
    VALUES ( AB[ID #] ),
    AB[Type] = "Type A"
    )
    VAR total =
    CALCULATE ( SUM ( AB[Amount] ), ALL ( AB ), VALUES ( AB[ID #] ) )
    RETURN
    IF ( Arows > 0, total, BLANK () )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • UdayReddy511's avatar
      UdayReddy511
      Frequent Visitor
      3 years ago

      If it is dynamic column (like Type), where i will insert random columns from other dimension tables which has different categoies instead of A/B...what is the syntax. Can you please let me know
      Thanks in advance.

  • 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.

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

    Hi RyndaRaw ,

    I prefer you to create a column to calculate easier not a measure in this case:

    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 total =
    CALCULATE ( SUM ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[ID#] ) )
RETURN
    IF (
        A > 0,
        IF (
            B > 0,
            IF ( 'Table'[Type] = "Type B", total ),
            total
        )
    )

    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.