Forum Discussion

TCGH's avatar
TCGH
Regular Visitor
2 years ago
Solved

Variance /slicer dependent/ need help for the formula

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('P...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
TCGH
Regular Visitor
2 years ago

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 VolumMeasureVARSPPProd VolumMeasureVARSPPProd VolumMeasureVARSPP
100000120000220000 
200000160000360000 

 

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

Plan 1 Plan 2 Plan Total  
Prod VolumMeasureVARSPPProd VolumMeasureVARSPPProd VolumMeasureVARSPP
10000200001200024000220000 
20000400001600032000360000 

 

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. 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.