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.