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.

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.