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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Nijlal01
Helper I
Helper I

Total as difference on multiple levels

Hi all,

 

I am trying to display the total as difference. Unfortunatly I do not get it to work on al levels. Anyone knows how to rewrite the DAX formula in order to display difference correctly?

 

KR,

 

Lars

 

Nijlal01_0-1629816378360.png

Difference = 
VAR x = 
CALCULATE(
    SUM(Sheet1[QTY]),
    FILTER(
        ALLSELECTED(Sheet1),
        Sheet1[PublishDate] < MAX(Sheet1[PublishDate])
    )
)
VAR y = 
CALCULATE(
    SUM(Sheet1[QTY]),
    FILTER( ALL(Sheet1), Sheet1[PublishDate] = MAX(Sheet1[PublishDate]) ) 
)
RETURN
IF(
    HASONEFILTER(Sheet1[PublishDate]),
    SUM(Sheet1[QTY]),
    x-y
)

 

1 ACCEPTED SOLUTION

Hi @Nijlal01 

I have a test by your sample and dax code. I find that your use AllSELECTED and ALL function in your X and Y code. Due to you use hierachy level in your Matrix, I think you don't need to use these two function to get data. But you need to use ALL function to get Max Date.

Update Code:

Difference =
VAR _MaxDate =
    MAXX ( ALL ( Sheet1 ), Sheet1[PublishDate] )
VAR _x =
    CALCULATE (
        SUM ( Sheet1[QTY] ),
        FILTER ( Sheet1, Sheet1[PublishDate] < _MaxDate )
    )
VAR _y =
    CALCULATE (
        SUM ( Sheet1[QTY] ),
        FILTER ( Sheet1, Sheet1[PublishDate] = _MaxDate )
    )
RETURN
    IF ( HASONEFILTER ( Sheet1[PublishDate] ), SUM ( Sheet1[QTY] ), _x - _y )

Result is as below.

1.png

Best Regards,
Rico Zhou

 

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

View solution in original post

7 REPLIES 7
Nijlal01
Helper I
Helper I

I did try to use the post @Greg_Deckler. Unfortunatly I did not get it to work 😞 There are more post about displaying total as difference however not one which explains how to do it on multiple levels. And I am to newbie to figure it out myself.

@Nijlal01 Can you post your sample data?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,

 

Thanks for the help! See data below:

 

PublishDate

ProductShipToWkNrQTY
1-1-2021100001A110
1-1-2021100001B115
1-1-2021100001B210
1-1-2021100002A115
1-1-2021100002A220
1-1-2021100002B220
2-1-2021100001A115
2-1-2021100001A125
2-1-2021100001B220
2-1-2021100002B215
2-1-2021100002A230
2-1-2021100002A110

Hi @Nijlal01 

I have a test by your sample and dax code. I find that your use AllSELECTED and ALL function in your X and Y code. Due to you use hierachy level in your Matrix, I think you don't need to use these two function to get data. But you need to use ALL function to get Max Date.

Update Code:

Difference =
VAR _MaxDate =
    MAXX ( ALL ( Sheet1 ), Sheet1[PublishDate] )
VAR _x =
    CALCULATE (
        SUM ( Sheet1[QTY] ),
        FILTER ( Sheet1, Sheet1[PublishDate] < _MaxDate )
    )
VAR _y =
    CALCULATE (
        SUM ( Sheet1[QTY] ),
        FILTER ( Sheet1, Sheet1[PublishDate] = _MaxDate )
    )
RETURN
    IF ( HASONEFILTER ( Sheet1[PublishDate] ), SUM ( Sheet1[QTY] ), _x - _y )

Result is as below.

1.png

Best Regards,
Rico Zhou

 

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

You are a genius! Thanks 😄

Greg_Deckler
Super User
Super User

@Nijlal01 See if MM3TR&R helps: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Ouch, it breaks my head but I will try if I can make it work using that post

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.