cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Natascha
Regular 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?

 

1 ACCEPTED SOLUTION
MFelix
Super User III
Super User III

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

2 REPLIES 2
MFelix
Super User III
Super User III

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

Hi MFelix,

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

Grtz, Natascha

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.