Forum Discussion

Natascha's avatar
Natascha
Frequent Visitor
5 years ago
Solved

DAX Delta with variables

Really standing at the beginning of the learning curve so excuse my rookie explanation. I'm trying to find a way to calculate Delta between two quantities selected in 1 slicer out of multiple options (so for the example 2 out of let's say 20 options are chosen). Tried a lot already and looked through the FAQ but I still need support.

 

So the variable is in the column header (= a slicer) and is referring to one of many plan snapshots.

 

In example   

REVENUE 2020M06 (snapshot) + REVENUE 2020M07 (snapshot) are chosen to calculate delta ---- slicer is named "Plan Name".

So someone could as well choose 2 different snapshots with slicer PLAN NAME eg:  APPROVED 2020M06 and SHIPPED 2020M07.

 

I suspect I have to get there via the SWITCH/selected value measure .... Until now: graciously failed 🙂

Can somebody point me in the right direction?

 

Show and Tell
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Natascha ,

     

    Don't know how you have the setup if you have different columns or a single column from where you select the slicer in my case I have a table with a column with the options to be selected and added the following measure:

    Delta = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALLSELECTED ( 'Table'[Option] ),
        'Table'[Option] = MAX ( 'Table'[Option] )
    )
)
    - CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table'[Option] ),
            'Table'[Option] = MIN ( 'Table'[Option] )
        )
    )

    Be aware that this makes the delta between the max and minimum value so if the user selectes more than two options the delta will be calculated between the first and the last value.

  • lisab78's avatar
    lisab78
    5 years ago

    I figured it out!

    Audience Growth Rates:=DIVIDE(CALCULATE

    (SUM(Data[AudienceTotal]),

    FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]=mAX('Calendar'[Date])))

    -CALCULATE(SUM(Data[AudienceTotal]),

    FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]=MIN('Calendar'[Date]))),

    CALCULATE(SUM(Data[AudienceTotal]),

    FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]=MAX('Calendar'[Date]))))

11 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi Natascha ,

     

    Don't know how you have the setup if you have different columns or a single column from where you select the slicer in my case I have a table with a column with the options to be selected and added the following measure:

    Delta = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALLSELECTED ( 'Table'[Option] ),
        'Table'[Option] = MAX ( 'Table'[Option] )
    )
)
    - CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table'[Option] ),
            'Table'[Option] = MIN ( 'Table'[Option] )
        )
    )

    Be aware that this makes the delta between the max and minimum value so if the user selectes more than two options the delta will be calculated between the first and the last value.

    • Natascha's avatar
      Natascha
      Frequent Visitor
      5 years ago

      Hi MFelix,

      You are the best! This actually works perfect! Thank you so much!

      Grtz, Natascha

    • lisab78's avatar
      lisab78
      Frequent Visitor
      5 years ago

      How would you use this to find the percentage difference between the two dates?

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

        Hi lisab78 ,

         

        What do you mean the percentage difference between the two dates?

    • lisab78's avatar
      lisab78
      Frequent Visitor
      5 years ago

      That works but gives me like 99.93%, I need it like the below screenshot that's how the formula would be in excel.

       

       

      Thank you for your assistance 🙂

       

      • lisab78's avatar
        lisab78
        Frequent Visitor
        5 years ago

        I figured it out!

        Audience Growth Rates:=DIVIDE(CALCULATE

        (SUM(Data[AudienceTotal]),

        FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]=mAX('Calendar'[Date])))

        -CALCULATE(SUM(Data[AudienceTotal]),

        FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]=MIN('Calendar'[Date]))),

        CALCULATE(SUM(Data[AudienceTotal]),

        FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]=MAX('Calendar'[Date]))))