Dynamic measures( Sum and Difference) for based on Slicer selection

[kkalyanrr]

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 select "Income" and any of the these "Expenses"/"Other Expenses"/"COst of Sales", we should get difference in the amount value.
When I select any of these("Expense"/"Other Expense"/"Cost of Sales") and any of these"Expense"/"Other Expense"/"Cost of Sales" , again we should get the sum of the respective amounts.

Thanks.

CategoryName	GLDate	Amount
Income	01-Jul-19	-1,994.24
Expenses	01-Jul-19	236.64
Other Expenses	01-Jul-19	28,338.00
Cost of Sales	01-Jul-19	14,789.34
Income	01-Jul-19	31,858.65
Expenses	01-Aug-19	3,10,951.91
Other Expenses	01-Aug-19	-46,357.47
Cost of Sales	01-Aug-19	19,582.30
Income	01-Aug-19	324.16
Expenses	01-Aug-19	50000
Other Expenses	01-Aug-19	-2,699.24
Cost of Sales	01-Aug-19	260.47
Income	01-Aug-19	-1,451.85
Expenses	01-Jul-19	5,730.00
Other Expenses	01-Aug-19	3,038.00
Cost of Sales	01-Jul-19	200
Income	01-Sep-19	2000
Expenses	01-Sep-19	-2,24,941.14
Other Expenses	01-Sep-19	659.48
Cost of Sales	01-Sep-19	65,577.28
Income	01-Sep-19	13,482.88
Expenses	01-Sep-19	6,658.00
Other Expenses	01-Sep-19	-1,083.40
Cost of Sales	01-Sep-19	-2,699.24
Income	01-Sep-19	253
Expenses	01-Aug-19	5,910.00
Other Expenses	01-Sep-19	65,577.28
Cost of Sales	01-Sep-19	13,482.88

[v-eqin-msft · Accepted solution]

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]

@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