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
    tamerj1
    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
    )
KimBoehmer's avatar
KimBoehmer
Helper I
3 years ago

Hi, 

 

thank you for your reply. 

 

Unfortunately this ist working.

tamerj1's avatar
tamerj1
Community Champion
3 years ago

KimBoehmer 

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

  • KimBoehmer's avatar
    KimBoehmer
    Helper I
    3 years ago

    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.

    • KimBoehmer's avatar
      KimBoehmer
      Helper I
      3 years ago

      Here the overview with your formular in comparison:

       

       

       

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        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
      tamerj1
      Community Champion
      3 years ago

      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.