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

[RyndaRaw]

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

[v-yingjl]

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.

[amitchandak]

@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]

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 · Accepted solution]

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.

[mahoneypat]

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]

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.