Forum Discussion
How to get the difference between more rows?
- 3 years ago
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] ) - 3 years ago
Hi KimBoehmer
Please refer to attached sample file with the solution. I've created a new column using power query to get the Source Number for sorting purposes.Value = VAR MeasureValue = SUM ( 'Zusammenfassung'[Wert] ) VAR T1 = ADDCOLUMNS ( VALUES ( 'Zusammenfassung'[Source.Name] ), "@Date", CALCULATE ( MAX ( 'Zusammenfassung'[Source.Number] ) ), "@Value", CALCULATE ( SUM ( 'Zusammenfassung'[Wert] ) ) ) 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 )
Yes i have the month and yes i will substract the latest minus the erliest i have filtered.
tamerj1 could you please help me? i am lost
- KimBoehmer3 years agoHelper I
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?
- tamerj13 years agoCommunity Champion
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 )
*UPDATEThe measure has been updated fixing one error KimBoehmer
- KimBoehmer3 years agoHelper I
Hi tamerj1 dont worry 🙂
i have tested your formular, but the result its unfortunately not correct 😞
Here two examples:
BR Kim
- tamerj13 years agoCommunity Champion
Please copy/paste the formula that you've used. Did you check the updated formula?
- KimBoehmer3 years agoHelper I
Yes, now it shows this:
- tamerj13 years agoCommunity Champion
For [Date] you need to use your month column. The month column has to be of YYYYMM format.
- KimBoehmer3 years agoHelper I
Hi, i did it but get no result:
The error is gone but there is no output