Forum Discussion

ilui's avatar
ilui
Frequent Visitor
6 years ago
Solved

Delta calculation based on dynamic Slicer

Hi,   I'm stuck with this New Measure field and I'd like to seek your help. We're taking Salesforce snapshot data and want to show comparison of forecast based on different snapshot date. Which sna...
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi ilui ,

    Based on your description, you can create this measure:

    Forecast Diff =
VAR _max =
    CALCULATE ( MAX ( 'Table'[Snapshot Date] ), ALLSELECTED ( 'Table' ) )
VAR _min =
    CALCULATE ( MIN ( 'Table'[Snapshot Date] ), ALLSELECTED ( 'Table' ) )
VAR _maxsum =
    CALCULATE ( SUM ( 'Table'[Forecast] ), 'Table'[Snapshot Date] = _max )
VAR _minsum =
    CALCULATE ( SUM ( 'Table'[Forecast] ), 'Table'[Snapshot Date] = _min )
RETURN
    _maxsum - _minsum

    To achieve the same visual, create two matrix to use visual overlay:

     

    Attached the sample file that hopes to help you: Forecast data.pbix

     

    Best Regards,
    Yingjie Li

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

v-yingjl's avatar
v-yingjl
Community Support
6 years ago

Hi ilui ,

Based on your description, you can create this measure:

Forecast Diff =
VAR _max =
    CALCULATE ( MAX ( 'Table'[Snapshot Date] ), ALLSELECTED ( 'Table' ) )
VAR _min =
    CALCULATE ( MIN ( 'Table'[Snapshot Date] ), ALLSELECTED ( 'Table' ) )
VAR _maxsum =
    CALCULATE ( SUM ( 'Table'[Forecast] ), 'Table'[Snapshot Date] = _max )
VAR _minsum =
    CALCULATE ( SUM ( 'Table'[Forecast] ), 'Table'[Snapshot Date] = _min )
RETURN
    _maxsum - _minsum

To achieve the same visual, create two matrix to use visual overlay:

 

Attached the sample file that hopes to help you: Forecast data.pbix

 

Best Regards,
Yingjie Li

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

  • ilui's avatar
    ilui
    Frequent Visitor
    6 years ago

    v-yingjl Thanks heaps! The formula works well but for some reason, the Forecast Diff show up Twice. Look like below

     

    I thought it's because of all the drilldown I have, but after removing the drilldown, it looks the same.

     

     2020017 20200810 
    CountryForecastForecast DiffForecastForecast Diff
    A$18,789,996$4,807,466$23,597,463$4,807,466
    B$12,156,438$2,350,168$14,506,605$2,350,168
    C$19,781,996$3,535,801$23,317,797$3,535,801
     $50,728,430$10,693,435$61,421,865$10,693,435

     

     

    I downloaded your sample but I cannot open it. Error message says "Object reference not set to an instance of an object"

     

    • v-yingjl's avatar
      v-yingjl
      Community Support
      6 years ago

      Hi ilui ,

      This is because measure cannot be used as a separate column in the matrix, it is by design in it.

      As I previous posted, you can create two matrix and superimpose them, set the date matrix arrange as 'Bring to front' in the Format pane, adjust the size of two matrix visuals properly.

       

      I have re-uploaded the sample file that you can refer it, please try to open it with the latest version of power bi desktop (August 2020) because it is currently known that there is a problem that the lower version cannot be used to open the higher version file.

      Sample file: Forecast.pbix 

       

      Best Regards,
      Yingjie Li

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

      • ilui's avatar
        ilui
        Frequent Visitor
        6 years ago

        v-yingjl Oh I missed the super imposed part. I'll try but I'm not sure if it'll work for my situation because my Matrix visual will have several level of drill down for people to look at the variances.

         

        If measures cannot be used as a separate column in the matrix, any alternative suggestion on how to show this better?

         

        Thanks a lot of your help!