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...
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]))))

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

        So if Jan 1 there are 15 items sold and then on Jan 31 there are 18 items sold, I have sold 3 more items at the end of the month and the percentage would be a 1.2% increase in units sold. The increase of % sold is what I need to find. I tried replacing the minus sign with the divide sign but it didn't give me the correct %.

  • 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]))))