Forum Discussion

kkalyanrr's avatar
kkalyanrr
Icon for Helper V rankHelper V
5 years ago
Solved

Dynamic 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...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 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)

     

     

    Here is the pbix file.

     

    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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

kkalyanrr , Try a measure like.

measure =
var _1 = countx(filter(allselected(Table),Table[Category] ="Income"),Table[Category])
var _2 = countx(filter(allselected(Table),Table[Category] <> "Income"),Table[Category])
return
if(isblank(_1) || isblank(_2), Sum(Table[Amount]), calculate(sumx(Table, if(Table[Category] ="Income", Table[Amount],-1* Table[Amount]))))

 

Better to have a category an independent slicer