topic Re: Total as difference on multiple levels in Developer

Total as difference on multiple levels

Nijlal01 — Tue, 24 Aug 2021 14:47:51 GMT

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 )

Re: Total as difference on multiple levels

Greg_Deckler — Tue, 24 Aug 2021 14:59:35 GMT

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

Re: Total as difference on multiple levels

Nijlal01 — Tue, 24 Aug 2021 15:24:51 GMT

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

Re: Total as difference on multiple levels

Nijlal01 — Wed, 25 Aug 2021 06:55:28 GMT

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.

Re: Total as difference on multiple levels

Greg_Deckler — Wed, 25 Aug 2021 12:42:26 GMT

@Nijlal01 Can you post your sample data?

Re: Total as difference on multiple levels

Nijlal01 — Wed, 25 Aug 2021 12:46:59 GMT

Hi @Greg_Deckler ,

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

Re: Total as difference on multiple levels

Anonymous — Thu, 26 Aug 2021 07:13:18 GMT

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.

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.

Re: Total as difference on multiple levels

Nijlal01 — Thu, 26 Aug 2021 07:47:37 GMT

You are a genius! Thanks 😄