Forum Discussion

reggiebob's avatar
reggiebob
Frequent Visitor
1 year ago
Solved

Column Value adjusted based on Slicer Selection

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: 

CustomerIdSite TypeOwner
1DogNational
2CatRegional
3CatNational
4HorseNone
5DogRegional
6FishNational
7GoatNone
8DogNone
9HorseNational
10GoatNational

 

Sales Table:

CustomerMonth Total Sales 
11/1/2025       65,123
21/1/2025       31,912
31/1/2025     987,416
41/1/2025         1,984
51/1/2025       15,189
61/1/2025 1,968,546
71/1/2025       11,654
81/1/2025     984,651
91/1/2025       49,843
101/1/2025         4,864
12/1/2025     516,854
22/1/2025       15,665
32/1/2025       16,581
42/1/2025       87,241
52/1/2025         8,946
62/1/2025       76,532
72/1/2025       64,352
82/1/2025       43,516
92/1/2025       31,331
102/1/2025     465,146
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

     

     

4 Replies

  • Deku's avatar
    Deku
    Super User
    1 year ago

    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

     

     

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

    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
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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