cancel
Showing results for
Did you mean:
Helper I

## 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 of the two products by date.

Example:

Oil  1/1/2019  2.00

Gas 1/1/2019  1.50

Tea  1/1/2019  0.50

Oil  2/1/2019  2.10

Gas 2/1/2019  1.60

Tea  2/1/2019  0.60

Oil  3/1/2019  2.30

Gas 3/1/2019  1.90

Tea  3/1/2019  1.00

I want two slicers both showing Oil, Gas and Tea.

If user selects say Oil from one and Gas from the other, then output Graph should show

Dates as the X Coordinate, Price as the Y coordinate and bars for Oil and Gas for each date. I would also like to be able to calculate the variance between Oil price and Gas price for each date.

1 ACCEPTED SOLUTION
Super User

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```
2 REPLIES 2
Super User

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```
Helper I

@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.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!