Column Value adjusted based on Slicer Selection

[reggiebob]

Hello,

I am trying to figure out how to accomplish the following and I am hoping someone has an idea of how it can be done: If you have a slicer with column values and a table visual with another columns values, and you deselect a value from the slicer the sales value for those that you deselected will automatically move to the "None" category in the table .

For example: in the below visual, if I deslelect "Dog" from the slicer those sales that have a Owner of "National" and "Regional" will be moved to the "None" column in the Summary table visual.

Any ideas? Thank you in advance for your help!

I am unable to upload the .pbix so here is the data.

Customer table: 

CustomerId	Site Type	Owner
1	Dog	National
2	Cat	Regional
3	Cat	National
4	Horse	None
5	Dog	Regional
6	Fish	National
7	Goat	None
8	Dog	None
9	Horse	National
10	Goat	National

Sales Table:

Customer	Month	Total Sales
1	1/1/2025	65,123
2	1/1/2025	31,912
3	1/1/2025	987,416
4	1/1/2025	1,984
5	1/1/2025	15,189
6	1/1/2025	1,968,546
7	1/1/2025	11,654
8	1/1/2025	984,651
9	1/1/2025	49,843
10	1/1/2025	4,864
1	2/1/2025	516,854
2	2/1/2025	15,665
3	2/1/2025	16,581
4	2/1/2025	87,241
5	2/1/2025	8,946
6	2/1/2025	76,532
7	2/1/2025	64,352
8	2/1/2025	43,516
9	2/1/2025	31,331
10	2/1/2025	465,146

[Anonymous · Accepted solution]

Hi @reggiebob ,

Do you want to count sales for unselected "Site Type" as "None"? If so, please try this.

Create a measure with below DAX.

TotalSales = VAR _selected = ALLSELECTED(Customer[Site Type])
VAR _maxOwner = MAX('_Owner'[Owner])
VAR _national = CALCULATE(SUM(Sales[ Total Sales ]),Customer[Owner] = _maxOwner)
VAR _noselected = CALCULATE(SUM(Sales[ Total Sales ]),NOT(Customer[Site Type] IN _selected))
RETURN IF( _maxOwner="None",_national+_noselected,_national)

The output.

——————————————————————————————————————————————————

If my answer helps you solve the problem, please accept my answer as a solution and let it be seen by more people in need.

Best regards,

Mengmeng Li

[reggiebob]

This is perfect. Thank you!

[ryan_mayu]

@reggiebob 

you can try to create a new table

sitetype = DISTINCT(Customer[Site Type])

then use the site type in the new table  to filter

create a measure

Measure = if (HASONEVALUE(sitetype[Site Type])&&max(Customer[Owner])="None",1)

add this measure to visual filter and set to not 1

pls see the attachment below

[Deku]

You can use a technique like the one presented by SQLBI.

You add another table with the "None" and have a measure calculate the sum for those that should appear