Forum Discussion
kkalyanrr
Helper V
5 years agoDynamic measures( Sum and Difference) for based on Slicer selection
Hello, I'm trying to achieve the following result from the given dataset, can you help me with the DAX. When I select "Income"in the slicer, we should get the sum of the amount values. When I se...
- Anonymous5 years ago
Hi kkalyanrr ,
Please try this measure after adding a new table for slicer:
Measure = VAR _all = ALLSELECTED ( forSlicer[Type] ) VAR countSele = COUNTROWS ( FILTER ( 'forSlicer', 'forSlicer'[Type] IN _all ) ) VAR _income = CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( 'Table', 'Table'[CategoryName] = "Income" ) ) VAR _sum = CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( 'Table', 'Table'[CategoryName] IN _all ) ) RETURN IF ( countSele = 1 && "Income" IN _all, _income, IF ( countSele >= 2 && "Income" IN _all, _income * 2 - _sum, _sum ) )My final output looks like this : ( measure in the lower left corner is just for test)
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.Best Regards,
Eyelyn Qin
Anonymous
5 years agoNot applicable
Hi kkalyanrr ,
Please try this measure after adding a new table for slicer:
Measure =
VAR _all =
ALLSELECTED ( forSlicer[Type] )
VAR countSele =
COUNTROWS ( FILTER ( 'forSlicer', 'forSlicer'[Type] IN _all ) )
VAR _income =
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER ( 'Table', 'Table'[CategoryName] = "Income" )
)
VAR _sum =
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER ( 'Table', 'Table'[CategoryName] IN _all )
)
RETURN
IF (
countSele = 1
&& "Income" IN _all,
_income,
IF ( countSele >= 2 && "Income" IN _all, _income * 2 - _sum, _sum )
)
My final output looks like this : ( measure in the lower left corner is just for test)
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin