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 am looking for a way to generate a subtotal column that's the sum of other rows of data, so in other words, it ignores / excludes the current row's value from the subtotal.
In case it's needed, you can access the sample PBI report here:
https://drive.google.com/file/d/1vGtN2pr8zHD_MSxANbs63KMcueOTcUPz/view?usp=sharing
Also, here is a link to the excel file used to create the PBI file and screenshot above: https://drive.google.com/file/d/19spH03FeAMPh6SWE2zTYqFsSy8cWeHl8/view?usp=sharing
Solved! Go to Solution.
@hasharitry this
_test =
IF (
OR (
OR ( ISINSCOPE ( SM[srg] ), ISINSCOPE ( SM[sds] ) ),
ISINSCOPE ( SM[loc] )
),
CALCULATE ( SUM ( CS1[metric1_subtotal] ), ALLSELECTED ( CS1 ) )
- SUMX ( CS1, CS1[metric1_subtotal] ),
IF (
HASONEVALUE ( SM[sdv] ),
VAR _1 =
MAX ( SM[sdv] )
VAR _2 =
CALCULATE (
SUMX ( CS1, CS1[metric1_subtotal] ),
FILTER ( ALLSELECTED ( SM ), SM[sdv] <> _1 )
)
RETURN
_2,
SUMX ( CS1, CS1[metric1_subtotal] )
)
)
@hasharican you try this as a measure?
_x :=
IF (
HASONEVALUE ( SM[sdv] ),
VAR _1 =
MAX ( SM[sdv] )
VAR _2 =
CALCULATE (
SUMX ( CS1, CS1[metric1_subtotal] ),
FILTER ( ALLSELECTED ( SM ), SM[sdv] <> _1 )
)
RETURN
_2,
SUMX ( CS1, CS1[metric1_subtotal] )
)
@smpa01 - Thanks for sharing this approach!
It seems to work fine at the 'sdv' level (Level-1), but when i drill down to 'srg' (Level-2), it doesn't seem to follow the drill-level rows.
Thoughts?
@hashari try this and see if it works as you desired
Measure :=
IF (
OR (
OR ( ISINSCOPE ( SM[srg] ), ISINSCOPE ( SM[sds] ) ),
ISINSCOPE ( SM[loc] )
),
SUMX ( CS1, CS1[metric1_subtotal] ),
IF (
HASONEVALUE ( SM[sdv] ),
VAR _1 =
MAX ( SM[sdv] )
VAR _2 =
CALCULATE (
SUMX ( CS1, CS1[metric1_subtotal] ),
FILTER ( ALLSELECTED ( SM ), SM[sdv] <> _1 )
)
RETURN
_2,
SUMX ( CS1, CS1[metric1_subtotal] )
)
)
@hashariI am not sure how do you envision the calculation to pan out on a drill down level
On individualsdv level (non-subtotal filter context), I am telling my measure to calculate evrything but that particular sdv level. So with sdv=2, it sums up 4+5+7+10.
When you drill down and gove to sdv->srg lvel what do you plan this measure to return for sdv-srg level with 2-20 and 2-51?
For 2-20 would it be possible to display 727 (870 - 143), instead of 640
For 2-51 would it be possible to display 783 (870 - 87), instead of 640
Also, would that carry if we drill down to Level-3, 'sds' ?
@hasharitry this
_test =
IF (
OR (
OR ( ISINSCOPE ( SM[srg] ), ISINSCOPE ( SM[sds] ) ),
ISINSCOPE ( SM[loc] )
),
CALCULATE ( SUM ( CS1[metric1_subtotal] ), ALLSELECTED ( CS1 ) )
- SUMX ( CS1, CS1[metric1_subtotal] ),
IF (
HASONEVALUE ( SM[sdv] ),
VAR _1 =
MAX ( SM[sdv] )
VAR _2 =
CALCULATE (
SUMX ( CS1, CS1[metric1_subtotal] ),
FILTER ( ALLSELECTED ( SM ), SM[sdv] <> _1 )
)
RETURN
_2,
SUMX ( CS1, CS1[metric1_subtotal] )
)
)
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |