DAX: sum with fslicers

[ddata]

Hi,

I have 3 columns: Country, Company and  Amount. When I use slicers on Country and Company, I need to get sum of  the amount for all the companies excluding the selected company in the selected country. How can I do this in DAX?

[v-yulgu-msft]

Hi @ddata,

Please try this:

Sum for unselected Company =
CALCULATE (
    SUM ( 'Test Table'[Amount] ),
    ALLEXCEPT ( 'Test Table', 'Test Table'[Country] )
)
    - CALCULATE ( SUM ( 'Test Table'[Amount] ) )

Best regards,
Yuliana Gu

[TheOckieMofo]

The answer here is probably using a technique called "disconnected slicers" if you read Rob Collie's book, or "parameter table" if you read the Italians (Russo & Ferrari). Rob Collie talks about it in a whole chapter in his book "Power Pivot and Power BI." I'd highly recommend buying that book. But since I can't link to that book, here's a link to the same/similar method used by the Italians:

Dax Patterns - Parameter Table

I'd follow what they do there with one simple change. Both of these methods use examples that show how the slicer can be used for INCLUSION. But it should be easy to alter to use for EXCLUSION.

Basically, the steps you'd follow would be:

Create disconnected tables that have all the options you want to display in the slicer. Each table should have two columns, one column being the options the end user sees and the other being an ascending number id, i.e. 1 to however many options

Next, create a "harvester" measure that will get the "max" of that slicer. The syntax would be something like:

     Harvester Measure:=MIN('Table Name'[Column Name]) 

Next, you'd write a switch function to define what each number meant. So that'd look like:

     Harvester Measure Definer:=Switch([Harvester Measure],1,"definition",2,definition....)

Next, create another measure that uses the result of that "harvester" measure in a filter statment. So, generally, that syntax would be:

      Final Measure:=CALCULATE(SUM(AMOUNT),FILTER('COUNTRY TABLE','COUNTRY TABLE[COUNTRY]<>[HARVESTER MEASURE DEFINER])))

It sounds a lot harder than it is. Learn this technique, it is very powerful and also can give the end user a lot of control over the report. Good luck!

[KHorseman]

Actually you can do this with a single regular measure.

Anti-Slicer Sum of Amount = VAR slicerselection = ALLSELECTED(TableName)
VAR everything = ALL(TableName)
VAR noneselected = IF(
	COUNTROWS(everything) - COUNTROWS(slicerselection) = 0,
	TRUE,
	FALSE
)
RETURN IF(
	noneselected = TRUE,
	SUM(TableName[Amount]),
	CALCULATE(
		SUM(TableName[Amount]),
		EXCEPT(everything, slicerselection)
	)
)

EXCEPT() does an anti-join on two tables, where it removes the rows in the 2nd table from the 1st table. You could do a simpler version of this...

Anti-Slicer Sum of Amount = CALCULATE(
	SUM(TableName[Amount]),
	EXCEPT(ALL(TableName), ALLSELECTED(TableName))
)

...but you'd run into one bit of weirdness: if you have nothing selected in your slicers, you'll get a result of 0. This is because having nothing selected in a slicer is the same as having everything selected in a slicer.  So I added in the "noneselected" variable to return a plain sum in those cases where nothing is selected.

The only other caveat I have to add is that I haven't done a thorough check to see what happens when something is selected in one slicer but nothing is selected in the other. Check your results with data you know and we'll see if a more complex join is required to account for this.

[TheOckieMofo]

I haven't really got into variables, but they seem super sexy. That's a pretty awesome solution.

[KHorseman · Accepted solution]

OK, I've tested it and as far as I can tell it works fine even if only one slicer has a selection. So that's good news. In testing this, I realized there's a much simpler way to get the same result. Take the sum of all Amount and subtract whatever you have selected. Not by subtracting table rows but by subtracting simple sums.

Sum of Amount Not Selected = CALCULATE(
	SUM(TableName[Amount]),
	ALL(TableName)
) -
SUM(TableName[Amount])

This does suffer the same problem as before; if nothing is selected in either slicer the result is 0. So we can use the same trick as before to get a regular sum in those cases:

Sum of Amount Not Selected = IF(
	COUNTROWS(ALL(TableName)) - COUNTROWS(ALLSELECTED(TableName)) = 0,
	SUM(TableName[Amount]),
	CALCULATE(
		SUM(TableName[Amount]),
		ALL(TableName)
	) -
	SUM(TableName[Amount])
)

I think this solution is about 10x less cool than my first idea, but it does have the advantage of being easier to wrap your head around. They both give the same results, so you can use whichever one you like.

[kcantor]

Nice job @KHorseman. How are you not an MVP by now? Those are both nice solutions. I'm just going to sit in the corner and stack blocks for a while and send you all of my tough calculations.

[KHorseman]

Thanks @kcantor. I have no idea how one becomes an MVP on here. Maybe there's a form or a voting process?

[KHorseman]

So you want kind of a negative slicer effect? You want the sum of Amount for everything that isn't selected in the slicer rather than what is selected?