cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ddata Occasional Visitor
Occasional Visitor

DAX: sum with fslicers

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: DAX: sum with fslicers

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.

8 REPLIES 8
Super User
Super User

Re: DAX: sum with fslicers

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?

Re: DAX: sum with fslicers

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!

Highlighted
Super User
Super User

Re: DAX: sum with fslicers

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.

Re: DAX: sum with fslicers

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

Super User
Super User

Re: DAX: sum with fslicers

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.

Super User
Super User

Re: DAX: sum with fslicers

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.

Super User
Super User

Re: DAX: sum with fslicers

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

Community Support Team
Community Support Team

Re: DAX: sum with fslicers

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.