cancel
Showing results for 
Search instead for 
Did you mean: 
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
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
    )

Great thank you ver much 🙂

tamerj1
Super User
Super User

Hi @KimBoehmer 
Please try

Value difference =
VAR __MEASURE_VALUE =
    SUM ( 'Zusammenfassung'[Value] )
RETURN
    SUMX (
        VALUES ( 'Zusammenfassung'[Source.Name] ),
        VAR __BASELINE_VALUE =
            CALCULATE ( SUM ( 'Zusammenfassung'[Value] ) )
        RETURN
            IF ( NOT ISBLANK ( __MEASURE_VALUE ), __MEASURE_VALUE - __BASELINE_VALUE )
    )

Hi, 

 

thank you for your reply. 

 

Unfortunately this ist working.

@KimBoehmer 

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

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 

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. 

Here the overview with your formular in comparison:

 

 

 

 

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

Thank you very much, but i have one last question 🙂

 

The difference is always postiv but i have positiv an negativ chnages in the numbers how can i display them in a chart?

 

@KimBoehmer 
Yes I made it this way as there is no logic (at least from my prespective) that defines which one minus which one. Please clarify this point. 

i want to compare the volumes from 2021 to 2022 for each month and of course some month are better than last year and others not. Therefore i would need plus and minus changes 

 

@KimBoehmer 

Is there a date or year-month column that defines the date of each list?

Do you to subtract the latest minus the earliest or the opposite?

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

@KimBoehmer 

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
    )

 


*UPDATE

The measure has been updated fixing one error @KimBoehmer 

 

Hi @tamerj1 dont worry 🙂 

 

i have tested your formular, but the result its unfortunately not correct 😞

 

Here two examples:

KimBoehmer_0-1663787715210.png

 

KimBoehmer_1-1663787745011.png

 

BR Kim

@KimBoehmer 

Please copy/paste the formula that you've used. Did you check the updated formula?

Yes, now it shows this:

KimBoehmer_0-1663789028473.png

 

@KimBoehmer 

For [Date] you need to use your month column. The month column has to be of YYYYMM format. 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors