cancel
Showing results 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

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

``````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
Community Support

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.

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.

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

Super User

@Nijlal01 Can you post your sample data?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper I

Thanks for the help! See data below:

 PublishDate Product ShipTo WkNr QTY 1-1-2021 100001 A 1 10 1-1-2021 100001 B 1 15 1-1-2021 100001 B 2 10 1-1-2021 100002 A 1 15 1-1-2021 100002 A 2 20 1-1-2021 100002 B 2 20 2-1-2021 100001 A 1 15 2-1-2021 100001 A 1 25 2-1-2021 100001 B 2 20 2-1-2021 100002 B 2 15 2-1-2021 100002 A 2 30 2-1-2021 100002 A 1 10
Community Support

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.

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.

Helper I

You are a genius! Thanks 😄

Super User

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper I

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

Announcements

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

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors