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