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('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. 


 
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.

     

5 Replies

  • TCGH's avatar
    TCGH
    Regular Visitor
    2 years ago

    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
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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