Variance /slicer dependent/ need help for the formula

[TCGH]

Hello

I am trying to visualize the variance between production plans. I tried a simple formula like this one:

SelectedCriteria = SELECTEDVALUE('SlicerTable'[Criteria])

Measure1 = CALCULATE(SUM('PRODUCTION'[Volumes]), 'PRODUCTION'[Plan] = SelectedCriteria)

Measure2 = CALCULATE(SUM('PRODUCTION'[Volumes]), 'PRODUCTION'[Plan] = OtherCriteria)

Variance = [Measure1] - [Measure2]

Or a DAX formula. it is always giving me a synthax error. So either I am not adapting the formula correctly or the formulas are not correct.

Let us try from the start. 
Within the same table of production volume, I have different production plans, let us say its plan 1 to 10.
In my visual, I have a table with the products and the volume. On top a slicer that shows the plans 1 to 10.
The slicer is multiple selection. 
What I can do now is to click on the slicer on plan 3 and 4 as an example, and below i can see the products and their planned volumes in 2 different colums, one under each plan. I need to create a variance between thse 2 plans to see what is difference in terms of production volume for each product. If I click on a thrid or 4th plan, they will show up. So then I am not sure what will the variance show up, it is great if it just show up in the last column and calculates the variance between the last 2 colums only. That way there is no mistakes in knowing what variance is calculated. 

any help is appreciated. 

[v-stephen-msft]

Hi @TCGH ,

If you want to compare the values of two slicers, it's a good idea to have fields from two different tables, and the tables of the two slicers are not related to your main table.

Here's an example for reference:

Measure = var _sel1=SELECTEDVALUE(Slicer1[Criteria])
var _sel2=SELECTEDVALUE('Slicer2'[Criteria])
return CALCULATE(SUM('Table'[Volumes]),FILTER('Table',[Criteria]=_sel1))-CALCULATE(SUM('Table'[Volumes]),FILTER('Table',[Criteria]=_sel2))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TCGH]

Hello Stephen
We are getting somewhere. 
I used your formula (without creating slicers in the table). By using one slicer from the Visualizations with multiple choice.
I updated the formula with the names of the table and data:

MeasureVARSPP = var _sel1=SELECTEDVALUE(PRODUCTION[Plan])

var _sel2=SELECTEDVALUE(PRODUCTION[Plan])

return CALCULATE(SUM('PRODUCTION'[Volumes]),FILTER('PRODUCTION',[Plan]=_sel1))+CALCULATE(SUM('PRODUCTION'[Volumes]),FILTER('PRODUCTION',[Plan]=_sel2))

Whereas when I select 2 Plans in the slicer, I get the following:

Plan 1		Plan 2		Plan Total
Prod Volum	MeasureVARSPP	Prod Volum	MeasureVARSPP	Prod Volum	MeasureVARSPP
10000	0	12000	0	22000	0
20000	0	16000	0	36000	0

I changed the minus into + in the formula and got this:

Plan 1		Plan 2		Plan Total
Prod Volum	MeasureVARSPP	Prod Volum	MeasureVARSPP	Prod Volum	MeasureVARSPP
10000	20000	12000	24000	22000	0
20000	40000	16000	32000	36000	0

Strangely, it is not showing me the variance, in both cases. 
It is only adding up and in the second case it shows a measure that is double (probably due to +) and the total stays a double. 

[v-stephen-msft · Accepted solution]

Hi @TCGH ,

If you select multiple plans in one slicer, you can't locate the one you selected.

As you can see, below both of these scribing formulas are the same, the computer doesn't recognize the first selected plan you are talking about, and the second plan.

You have to create two tables like I did to implement your first slicer's choice and second slicer's choice, and the two slicers were not related to each other.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TCGH]

Hi
I view your youtube and tried your solution and came up with this formula:

MeasureVol = CALCULATE(SUM('PRODUCTION'[Volumes]),Filter(all('PRODUCTION'[Plan]),'PRODUCTION'[Plan] in ALLSELECTED('PRODUCTION'[Plan])))

What I end up with is having one additional measured column which is equal to the prod plan I selected in the slicer. When I select the second plan, I get the same: an additional column with the same volume nb. 

What I need is the variance between these columns. I want only one column showing Plan 1 - Plan 2.

How can I customzie the formula something like that

MeasureVol = CALCULATE(SUM('PRODUCTION'[Volumes]),Filter(all('PRODUCTION'[Plan]),'PRODUCTION'[Plan] in ALLSELECTED('PRODUCTION'[Plan]))) - CALCULATE(SUM('PRODUCTION'[Volumes]),Filter(all('PRODUCTION'[Plan]),'PRODUCTION'[Plan] in ALLSELECTED('PRODUCTION'[Plan])))

Whereas the first part should be for Plan 1 and second part for plan 2 in a way that it recognized that the first selection from the slicer is plan 1 and second is part 2

[amitchandak]

@TCGH , if want compare two categorical values using slicer, consider

Compare Categorical Data Using Slicers, Compare Two Brands/Categories/Cities: https://youtu.be/exN4nTewgbc