topic Re: New DAX measure to sum values that exist in prior column in DAX Commands and Tips

New DAX measure to sum values that exist in prior column

turp111 — Tue, 07 Nov 2023 16:28:24 GMT

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}

)

Re: New DAX measure to sum values that exist in prior column

Rupak_bi — Tue, 07 Nov 2023 17:05:39 GMT

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

Re: New DAX measure to sum values that exist in prior column

Anonymous — Mon, 27 Nov 2023 01:42:05 GMT

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