cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Raymo3u99
Helper I
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
d_gosbell
Super User
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.

 

 

2019-05 variance.png

 

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

View solution in original post

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

 

 

2019-05 variance.png

 

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

View solution in original post

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.