Forum Discussion

KimBoehmer's avatar
KimBoehmer
Helper I
3 years ago
Solved

How to get the difference between more rows?

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 w...
  • tamerj1's avatar
    tamerj1
    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] )
  • tamerj1's avatar
    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
        )