Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.).
Solved! Go to 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
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
Hae you tried calculate sum of amount filtering all (child IDs) ??
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
28 | |
21 | |
12 | |
8 |
User | Count |
---|---|
74 | |
51 | |
45 | |
16 | |
12 |