Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
KimBoehmer
Helper I
Helper I

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 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?
2 ACCEPTED SOLUTIONS

@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] )

View solution in original post

tamerj1
Super User
Super User

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.

1.png2.png3.png

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
    )

View solution in original post

22 REPLIES 22

Hi, i did it but get no result:

 

KimBoehmer_0-1663821619030.png

The error is gone but there is no output 

 

 

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

KimBoehmer_0-1663784527940.png

 

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

 

Do you know what i mean?

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.