Calculate a measure using multiple selected values in a slicer

DAX DAX Calculations
1

I have a sales table that contains the store number and sales amount. I want to create a visualization that will display a complete list of stores, but I want to calculate the total sales only for the store numbers that I select. If I use a standard slicer that interacts with the visualization, it will limit the stores in the list, to the selection that I make. I would like to be able to select a number of stores in the slicer , with the full list of stores remaining in the visualization. Calculate the sales for the selected stores and diaplay 0 for the others.
I attached an image here that I think will explain what I am looking for. How do I display a fiull list of stores, but calculate the sales amount for the stores that I have selected DNAEDNA%20Multiple%20selections

2

You may think it’s crazy how to solve this, but after quite a bit of testing I think this is the best way.

First you actually need to create another lookup table with the location/store in it.

Like so

image

Then create similar relationship to the existing table

image

Then use the below formula

image
image.png902×415 19.3 KB

The inner working of DAX don’t really make this too easy.

I’ve try a number of things with this and have really landed here as the best one.

Chrs

3

I can seewhere you are going with this but It is not working for me.

I did create the new table as you have suggested.

The selection slicer is on the bottom right where I have selected 2 items from the list.
The number 2 is from your VAR expression for me to see what value is there when I choose from the list
The table on the left is displaying a collection of measures and I want the measures to calculate only if the value in the first column is the same as the value that is selected, and you can see that this is not happening.

my measure -->

Inclusion = 
 VAR Selected = COUNTROWS( INTERSECT( Values( POS[DMA Code Name]), values('DMA Comparison'[DMA Code Name] )))
RETURN 
 if( isblank( Selected), 0, POS[P: POS Units])

SAM%20Image
SAM Image.jpg1077×633 219 KB

4

By looking over this a few times it just looks like there’s a mix up in the order within the INTERSECT

Here’s a list of the key things

  1. The slicer values need to be the existing location dimension
  2. In the table need to be the dimension from the new comparison table
  3. Then within intersect you are checking is the comparison location (at every row) exist within the selected location within the slicer
  4. If at each row that location does not exist within the table made up of the slicer selections then that needs to equal zero

See if this fixes it.