Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculate a Variance between two dynamic cards that are each being filtered individually

I'm currently trying to calculate a variance between two cards and have it display on the third card. This is a lot trickier than just a simple DAX calculation as currently the two values are each being changed by their own individual filter on the report. As the user selects the "Type" which in this case will be "Actuals" for the first card to show actual values, and then select "Plan" in the second filter to display the Plan values. I want to somehow create a third card that will actively change with these two cards to calculate the variance between them. 

 

You can see in the screenshot below a better look at what I'm trying to explain; each value is coming from the same one "Value" column within the date. Currently the cards below the variance are showing just an overall total as I have not been able to figure out if this type of calculation is even possible but you can see the type of data I'm trying to show.

Any assistance would be greatly appreciated or if anyone knows that this jsut something that can't be done through power bi. 

Need Help

2 Replies

  • AntonioM's avatar
    AntonioM
    Icon for Solution Sage rankSolution Sage
    4 years ago

    You'll need to use measures here. What do the first two cards use to show their values? If they both used a measure, then you could create a measure for the variance that's just [measure1] - [measure2].

     

    You might need two tables with the list of filter options, one for each slicer, then the measures would be something like

    measure1 = CALCULATE( SUM ( Table[Value] ), Table[Type] =  SELETCEDVALUE ( Slicer1[Type] ) )
    measure2 = CALCULATE( SUM ( Table[Value] ), Table[Type] =  SELETCEDVALUE ( Slicer2[Type] ) )

    Then for the variance you can just write

    Variance = [measure1] - [measure2]

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This was very helpful thank you! I'm still running into a few problems after using this method;

       

      First, I was recieving the "'SELECTEDVALUE' has been used in a True/False expression" error which I was able to get around by creating a variable for the selected value. 

      Now I have the first two columns producing the correct values after I created the two tables and two measures using them accordingly. When I try to do the variance of subtracting the two measures, I'm receiving '(Blank)' value returns within each card. I tried testing just to display the Variance=[measure1] and that actually returned black as well as seen below

       

      One of my guesses was that it may be due to interactions with the filters so I'm playing around with that but still haven't figured out why they are returning blank... 

       

      I appreciate the assistance and any more help would be great!