Forum Discussion

rpinxt's avatar
rpinxt
Solution Sage
2 years ago

Calculate the variance between two (variable) selections

I have this simplified view:

As you can see now LBE1 and LBE2 are selected.

I want to ad a 3rd field (measure) which will give the variance between the selections.

The slicer on the right is based on a field in the data, so each row in the data has one of these 5 in a column named 'Type'.

 

How can I make a measure that subtracts the value belonging with LBE2 with the value beloning with LBE1.

And of course if I select LBE2 and LBE3 or even LBE1 and LBE3 (LBE3, LBE4 etc etc) it still would work and subtract the values belonging to that combination.

 

Need Help

6 Replies

  • rpinxt's avatar
    rpinxt
    Solution Sage
    2 years ago

    Maybe posting the underlying data will help to get this solved.

     

    YearMthTypeAddendum
    20241LBE1340635
    20241LBE2275680
    20242LBE1290012
    20242LBE2287260
    20243LBE1314827
    20243LBE2298206
    20244LBE1283520
    20244LBE2321729
    20245LBE1270133
    20245LBE2319776
    20246LBE1259413
    20246LBE2319392
    20247LBE1256339
    20247LBE2272356
    20248LBE1233174
    20248LBE2252100
    20249LBE1273970
    20249LBE2286258
    202410LBE1264020
    202410LBE2253540
    202411LBE1257562
    202411LBE2257523
    202412LBE1222870
    202412LBE2223364
  • rpinxt's avatar
    rpinxt
    Solution Sage
    2 years ago

    Maybe harder then I thought.

     

    Can it not be done?

    Or every other way to do this would be appreciated to.

    Would think there would be somekind of formula to show variance in a matrix.

  • rpinxt's avatar
    rpinxt
    Solution Sage
    2 years ago

    Well maybe it is just not possible with the current data setup.

    How would you guys do something like this?
    How would your data look?

    Cannot be the first one that wants to look at his data in this way?? 🤔

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi, rpinxt 

      You can implement the calculation variance in total. I've tried adding another measure to the matrix, but that would have it in every column of the matrix:

      As you can see, while the variance of the selected two can be successfully calculated, this will put the measure of this variance into the columns of each matrix.

      Instead, you can calculate the variance for two columns in the matrix and display it in the total.

      Sum of Addendum =
IF (
    NOT HASONEVALUE ( 'Table'[Type] ),
    AVERAGEX (
        'Table',
        CALCULATE ( SUM ( 'Table'[Addendum] ), 'Table'[Type] = "LBE1" )
            - CALCULATE ( SUM ( 'Table'[Addendum] ), 'Table'[Type] = "LBE2" )
    ),
    SUM ( 'Table'[Addendum] )
)

      You can then control your slicer options, check the two columns you want to show variance, and uncheck the other columns. I've provided the PBIX file used this time below.

       

       

       

      How to Get Your Question Answered Quickly

      If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

      Best Regards

      Jianpeng Li

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

       

  • rpinxt's avatar
    rpinxt
    Solution Sage
    2 years ago

    Thanks Anonymous but you made a "fixed" solution where you compare LBE2 with LBE1, but the user can make every selection from the slicer. So also LBE3 with LBE1 or LBE2 with LBE3.

     

    Also in your measure you are treating field addendum as a field of a table in the averagex. But it is not a table field it is a measure.

     

    I compiled a sample workbook here so you can see the general setup and data (real file is much bigger and complex but these fields and relations are needed for that visual) :

    https://drive.google.com/file/d/10VpjOCSx1eyqixI14mklcSlL-Cti0Y-e/view?usp=sharing

     

    So I have field parameter to switch between the value fields (prmValueSelection) and a slicer for the Type in which user can select LBE1, LBE2, LBE3 etc.

     

    The goal now is when 2 types are selected to get the difference in selected value of these two types.

    (user should not select more then 2 but if he/she does result should just be blank, 0, error or whatever message)

     

    I do not think it can be done but if somebody could find a way to achieve this that would be great.

     

  • PBIdashboards's avatar
    PBIdashboards
    Post Patron
    2 months ago

    For dynamic variance between two slicer-selected values, the DAX pattern uses SELECTEDVALUE with a disconnected table:

     
     
    Variance =
    VAR _sel1 = CALCULATE([Amount], 'Type'[Type] = SELECTEDVALUE(Slicer1[Type]))
    VAR _sel2 = CALCULATE([Amount], 'Type'[Type] = SELECTEDVALUE(Slicer2[Type]))
    RETURN _sel1 - _sel2

    This requires two separate single-select slicers (one per selection). If users need to pick from the same list, you need a disconnected parameter table with two separate What-If parameters one for each selection slot.

    The complexity grows quickly if you have multiple metrics or need the variance to work across a hierarchy drill-down.

    For this exact use case users picking any two scenarios to compare and seeing variance automatically Flexa Tables on AppSource removes the DAX entirely. Users select the two comparison periods directly in the published report, variance columns appear instantly across all metrics. Particularly clean for LBE vs Actuals or Budget vs Forecast type comparisons