New DAX measure to sum values that exist in prior column

[turp111]

I have a matrix visual with several columns per Plan ID.  I'm trying to add one more DAX measure/column that will sum the values in the Comp Amount column for each Child Component, per Parent Component.  The pic below is kinda oversimplified (and I did it in Excel just for dispay purposes) but it gets the idea across.  The circled column in the pic here is what I'm wanting to add.  You can see that for each Parent Component there are multiple Child Components.  I just need one more column with total PER Parent Component...PER Plan ID.  So there could be more PLan IDs listed here with the same type of look.  Below are some of the expressions I've tried to no avail.  I'm sure I'm just missing something small.  Thanks! 

FYI - the POS_NEG_AMOUNTS value in the DAX expression below are the amount values I have in my table (ex: -500, 200.75, -1033.48, etc.).

0.03 - COST_OF_COMP_CARD =

VAR COC_TYPE_ID = CALCULATE(

        MAX('Breakdown Table (plancomcycledtl)'[TRC ICUFEECOMPONENT.FEE_COMPONENT_TYPE_ID]),

        'Breakdown Table (plancomcycledtl)'[TRC ICUFEECOMPONENT.FEE_COMPONENT_TYPE_ID] in {"0000011", "0000036", "0000075", "0000074", "0000073", "0000072", "0000078", "0000077"}

)

VAR NW_CHARGE_GROUP = CALCULATE(

        MAX('Breakdown Table (plancomcycledtl)'[TRC ICUFEECOMPONENT.FEE_COMPONENT_TYPE_ID]),

        FILTER('Breakdown Table (plancomcycledtl)', 'Breakdown Table (plancomcycledtl)'[TRC ICUFEECOMPONENT.FEE_COMPONENT_TYPE_ID] in {"0000002", "0000001", "0000004", "0000005", "0000006", "0000007", "0000008", "0000009", "0000010"})

)

RETURN

CALCULATE(

        SUM('Breakdown Table (plancomcycledtl)'[POS_NEG AMOUNTS]),

        'Breakdown Table (plancomcycledtl)'[TRC ICUFEECOMPONENT.FEE_COMPONENT_TYPE_ID] in {COC_TYPE_ID, NW_CHARGE_GROUP}

)

[Anonymous · Accepted solution]

Hi @turp111 ,

You can create a measure or calculated column as below to get it, please find the details in the attachment.

Create a calculated column:

Column = 
VAR _mincid =
    CALCULATE (
        MIN ( 'Breakdown Table (plancomcycledtl)'[Child Comp ID] ),
        FILTER (
            ALLSELECTED ( 'Breakdown Table (plancomcycledtl)' ),
            'Breakdown Table (plancomcycledtl)'[Plan ID]
                = EARLIER ( 'Breakdown Table (plancomcycledtl)'[Plan ID] )
                && 'Breakdown Table (plancomcycledtl)'[Parent Component]
                    = EARLIER ( 'Breakdown Table (plancomcycledtl)'[Parent Component] )
        )
    )
VAR _pamount =
    CALCULATE (
        SUM ( 'Breakdown Table (plancomcycledtl)'[Comp Amount] ),
        FILTER (
            ALLSELECTED ( 'Breakdown Table (plancomcycledtl)' ),
            'Breakdown Table (plancomcycledtl)'[Plan ID]
                = EARLIER ( 'Breakdown Table (plancomcycledtl)'[Plan ID] )
                && 'Breakdown Table (plancomcycledtl)'[Parent Component]
                    = EARLIER ( 'Breakdown Table (plancomcycledtl)'[Parent Component] )
        )
    )
RETURN
    IF ( 'Breakdown Table (plancomcycledtl)'[Child Comp ID] = _mincid, _pamount )

Create a mesaure:

Parent Amount = 
VAR _pid =
    SELECTEDVALUE ( 'Breakdown Table (plancomcycledtl)'[Plan ID] )
VAR _parentcmp =
    SELECTEDVALUE ( 'Breakdown Table (plancomcycledtl)'[Parent Component] )
VAR _ccid =
    SELECTEDVALUE ( 'Breakdown Table (plancomcycledtl)'[Child Comp ID] )
VAR _mincid =
    CALCULATE (
        MIN ( 'Breakdown Table (plancomcycledtl)'[Child Comp ID] ),
        FILTER (
            ALLSELECTED ( 'Breakdown Table (plancomcycledtl)' ),
            'Breakdown Table (plancomcycledtl)'[Plan ID] = _pid
                && 'Breakdown Table (plancomcycledtl)'[Parent Component] = _parentcmp
        )
    )
VAR _pamount =
    CALCULATE (
        SUM ( 'Breakdown Table (plancomcycledtl)'[Comp Amount] ),
        FILTER (
            ALLSELECTED ( 'Breakdown Table (plancomcycledtl)' ),
            'Breakdown Table (plancomcycledtl)'[Plan ID] = _pid
                && 'Breakdown Table (plancomcycledtl)'[Parent Component] = _parentcmp
        )
    )
RETURN
    IF ( _ccid = _mincid, _pamount )

Best Regards

[Rupak_bi]

Hae you tried calculate sum of amount filtering all (child IDs)  ??