Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Natascha
Frequent Visitor

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   Capture delta.PNG

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?

 

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

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

MFelix_0-1610797552792.png

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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

View solution in original post

11 REPLIES 11
MFelix
Super User
Super User

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

MFelix_0-1610797552792.png

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

lisab78_1-1620074515731.png

 

 

Thank you for your assistance 🙂

 

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

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

Hi @lisab78 ,

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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 %.

Hi @lisab78 ,

 

You refer two dates within the same month how are you setting up your visualization? is it by day by month?

 

If it's by month you would need to make a measure similar to this one:

Percentage =
DIVIDE (
    CALCULATE (
        SUM ( Table[Column] ),
        FILTER ( ALL ( Table[Date] ), Table[Date] = MAX ( Table[Date] ) )
    ),
    CALCULATE (
        SUM ( Table[Column] ),
        FILTER ( ALL ( Table[Date] ), Table[Date] = MIN ( Table[Date] ) )
    )
)

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



I don't have the option to click to accept the solution in the reply.

Hi Lisa

 

I selected your answer since I assume that is the corret answer,


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you, took me a bit but I figured it out 🙂

Hi MFelix,

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

Grtz, Natascha

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.