topic Re: How to get the difference between more rows? in DAX Commands and Tips

How to get the difference between more rows?

KimBoehmer — Tue, 20 Sep 2022 05:58:15 GMT

Hey guys,

i need your help please i hava a huge table with lot of data (many excel files in one folder) and i want the differences between the Sources (excelfiles).

I have found something which is almost good for that what i want in the Quick Measures:

Value difference from LIST_211022_MOT_PROG_P2022-01_final.xlsx =

VAR __BASELINE_VALUE =

CALCULATE(

SUM('Zusammenfassung'[Value]),

'Zusammenfassung'[Source.Name]

IN { "LIST_211022_MOT_PROG_P2022-01_final.xlsx" }

)

VAR __MEASURE_VALUE = SUM('Zusammenfassung'[Value])

RETURN

IF(NOT ISBLANK(__MEASURE_VALUE), __MEASURE_VALUE - __BASELINE_VALUE)

But the problem here ist that the Source is not dinamic, i have to filter between the differen excel files and need that den differences.

Almost like the function in the pivot tabel (show values as difference).

Can you help me please?

Re: How to get the difference between more rows?

tamerj1 — Tue, 20 Sep 2022 06:57:56 GMT

Hi KimBoehmer
Please try

Value difference = VAR __MEASURE_VALUE = SUM ( 'Zusammenfassung'[Value] ) RETURN SUMX ( VALUES ( 'Zusammenfassung'[Source.Name] ), VAR __BASELINE_VALUE = CALCULATE ( SUM ( 'Zusammenfassung'[Value] ) ) RETURN IF ( NOT ISBLANK ( __MEASURE_VALUE ), __MEASURE_VALUE - __BASELINE_VALUE ) )

Re: How to get the difference between more rows?

KimBoehmer — Tue, 20 Sep 2022 07:08:37 GMT

Hi,

thank you for your reply.

Unfortunately this ist working.

Re: How to get the difference between more rows?

tamerj1 — Tue, 20 Sep 2022 07:53:20 GMT

KimBoehmer

How does your visual look like? What results did you get?

Re: How to get the difference between more rows?

KimBoehmer — Wed, 21 Sep 2022 05:23:09 GMT

Hi,

i get no result, there is no mistake in the formular.

and i have a slicer with the different Sources, i only want to compare two different sources at the same time.

Re: How to get the difference between more rows?

KimBoehmer — Wed, 21 Sep 2022 05:22:37 GMT

Here the overview with your formular in comparison:

Re: How to get the difference between more rows?

tamerj1 — Tue, 20 Sep 2022 18:41:27 GMT

KimBoehmer

So you want to select two sources from the slicer and the display the values of the two sources in addition to the difference between them? Is that correct? If so, I would suggest to use a table visual as it has a total where we csn disply the difference value.

Re: How to get the difference between more rows?

tamerj1 — Tue, 20 Sep 2022 19:03:22 GMT

KimBoehmer
If you want to follow my suggestion and disply the results in a tablevisual, then place the Sorce Name column in the table along with the following measure. From the visual format options, activate the total and rename it "Difference"

Value = VAR MeasureValue = SUM ( 'Zusammenfassung'[Value] ) VAR T1 = ADDCOLUMNS ( VALUES ( 'Zusammenfassung'[Source.Name] ), "@Value", CALCULATE ( SUM ( 'Zusammenfassung'[Value] ) ) ) VAR DifferenceValue = MAXX ( T1, [@Value] ) - MINX ( T1, [@Value] ) RETURN IF ( HASONEVALUE ( 'Zusammenfassung'[Source.Name] ), MeasureValue, DifferenceValue )

If you wish to display the difference in a seperate chart, then place the following measure in the chart ALONE (don't place any column along with it in the same chart)

Value difference = VAR T1 = ADDCOLUMNS ( VALUES ( 'Zusammenfassung'[Source.Name] ), "@Value", CALCULATE ( SUM ( 'Zusammenfassung'[Value] ) ) ) RETURN MAXX ( T1, [@Value] ) - MINX ( T1, [@Value] )

Re: How to get the difference between more rows?

KimBoehmer — Wed, 21 Sep 2022 05:16:03 GMT

Thank you very much, but i have one last question 🙂

The difference is always postiv but i have positiv an negativ chnages in the numbers how can i display them in a chart?

Re: How to get the difference between more rows?

tamerj1 — Wed, 21 Sep 2022 05:23:53 GMT

KimBoehmer
Yes I made it this way as there is no logic (at least from my prespective) that defines which one minus which one. Please clarify this point.

Re: How to get the difference between more rows?

KimBoehmer — Wed, 21 Sep 2022 06:00:20 GMT

i want to compare the volumes from 2021 to 2022 for each month and of course some month are better than last year and others not. Therefore i would need plus and minus changes

Re: How to get the difference between more rows?

tamerj1 — Wed, 21 Sep 2022 06:17:02 GMT

KimBoehmer

Is there a date or year-month column that defines the date of each list?

Do you to subtract the latest minus the earliest or the opposite?

Re: How to get the difference between more rows?

KimBoehmer — Wed, 21 Sep 2022 06:24:43 GMT

Yes i have the month and yes i will substract the latest minus the erliest i have filtered.

Re: How to get the difference between more rows?

KimBoehmer — Wed, 21 Sep 2022 18:13:12 GMT

tamerj1 could you please help me? i am lost

Re: How to get the difference between more rows?

KimBoehmer — Wed, 21 Sep 2022 18:22:49 GMT

Here the example ( the older version is on top, the newest version on the bottom):

Normaly the difference between these two should be negative and not positiv.

Do you know what i mean?

Re: How to get the difference between more rows?

tamerj1 — Thu, 22 Sep 2022 04:55:20 GMT

KimBoehmer

Apologies for the late response. I was extremely busy tody I didn't have the chance to look into it.
Actually, this shouldn't be a problem we just need to filter the table or sort it by date then retrieve the amount for both max and min dates.

Value = VAR MeasureValue = SUM ( 'Zusammenfassung'[Value] ) VAR T1 = ADDCOLUMNS ( VALUES ( 'Zusammenfassung'[Source.Name] ), "@Date", CALCULATE ( MAX ( 'Zusammenfassung'[Date] ) ), "@Value", CALCULATE ( SUM ( 'Zusammenfassung'[Value] ) ) ) VAR FirstRecod = TOPN ( 1, T1, [@Date], ASC ) VAR LastRecod = TOPN ( 1, T1, [@Date] ) VAR DifferenceValue = MAXX ( LastRecod, [@Value] ) - MAXX ( FirstRecod, [@Value] ) RETURN IF ( HASONEVALUE ( 'Zusammenfassung'[Source.Name] ), MeasureValue, DifferenceValue )

*UPDATE

The measure has been updated fixing one error KimBoehmer

Re: How to get the difference between more rows?

KimBoehmer — Wed, 21 Sep 2022 19:16:54 GMT

Hi tamerj1 dont worry 🙂

i have tested your formular, but the result its unfortunately not correct 😞

Here two examples:

BR Kim

Re: How to get the difference between more rows?

tamerj1 — Wed, 21 Sep 2022 19:33:59 GMT

KimBoehmer

Please copy/paste the formula that you've used. Did you check the updated formula?

Re: How to get the difference between more rows?

KimBoehmer — Wed, 21 Sep 2022 19:37:17 GMT

Yes, now it shows this:

Re: How to get the difference between more rows?

tamerj1 — Thu, 22 Sep 2022 01:59:32 GMT

KimBoehmer

For [Date] you need to use your month column. The month column has to be of YYYYMM format.