cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Raymo3u99 Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Super User
Super User

Re: Comparing values from 2 different selections

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
Super User
Super User

Re: Comparing values from 2 different selections

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

Raymo3u99 Frequent Visitor
Frequent Visitor

Re: Comparing values from 2 different selections

@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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 138 members 1,554 guests
Please welcome our newest community members: