topic Re: Comparing values from 2 different selections in Report Server

Comparing values from 2 different selections

Raymo3u99 — Wed, 29 May 2019 18:00:59 GMT

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.

Re: Comparing values from 2 different selections

d_gosbell — Wed, 29 May 2019 23:41:52 GMT

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

Re: Comparing values from 2 different selections

Raymo3u99 — Thu, 30 May 2019 15:55:00 GMT

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.