Forum Discussion

Raymo3u99's avatar
Raymo3u99
Helper I
7 years ago
Solved

Comparing values from 2 different selections

I have a table with the following columns:   Product, Date, Cost   I want to be able to have two slicers showing product. User can select a product from each slicer and I need to compare the cost...
calculated tables
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    So the first part of your requirement you could just do with a single slicer on Product that is set to allow multiple selections.

     

     

     

    Calculating the variance of a multi-select is a bit more involved, but not impossible (see the table on the right above). I did this with the following code:

     

    Variance = 
var _selectedProducts = ALLSELECTED(Table1[Product])
var _countSelected = COUNTROWS(_selectedProducts)
var _prod1 = MINX(_selectedProducts,[Product])
var _prod2 = MAXX(_selectedProducts,[Product])
var _prod1Cost = CALCULATE(sum(Table1[Cost]), Table1[Product] = _prod1)
var _prod2Cost = CALCULATE(sum(Table1[Cost]), Table1[Product] = _prod2)
var _result = IF(_countSelected = 2 && SELECTEDVALUE(Table1[Product]) = _prod1, _prod2Cost - _prod1Cost)
RETURN _result
d_gosbell's avatar
d_gosbell
Super User
7 years ago

So the first part of your requirement you could just do with a single slicer on Product that is set to allow multiple selections.

 

 

 

Calculating the variance of a multi-select is a bit more involved, but not impossible (see the table on the right above). I did this with the following code:

 

Variance = 
var _selectedProducts = ALLSELECTED(Table1[Product])
var _countSelected = COUNTROWS(_selectedProducts)
var _prod1 = MINX(_selectedProducts,[Product])
var _prod2 = MAXX(_selectedProducts,[Product])
var _prod1Cost = CALCULATE(sum(Table1[Cost]), Table1[Product] = _prod1)
var _prod2Cost = CALCULATE(sum(Table1[Cost]), Table1[Product] = _prod2)
var _result = IF(_countSelected = 2 && SELECTEDVALUE(Table1[Product]) = _prod1, _prod2Cost - _prod1Cost)
RETURN _result
Raymo3u99's avatar
Raymo3u99
Helper I
7 years ago

d_gosbell  Thanks for the solution. This worked for me. The requirement was to have two slicers - From Product and To Product. So I created two Distinct(product) tables and then created slicers using those table. I had to then tweat your variance code a little. 

 

Thank You.