Forum Discussion

SchmidMatt84's avatar
SchmidMatt84
New Member
1 year ago
Solved

Calculation of difference between two rows

Hi I need to calcualte the difference between two values which is different depending on a "version"-Value. If its the version "Basis" the difference should be calculated bewtween the years (eg. 202...
  • FBergamaschi's avatar
    FBergamaschi
    1 year ago

    I think a visual calculation here would be the best approach anyway so I try to solve it immediately using the names in the visual picture you sent

     

    Difference Visual CALC =
    VAR MinYear = MINX ( ROWS, [Jahr] )

    VAR CurrentYear = [Jahr]
    RETURN

    IF (
          [Variante] = "Basis" && CurrentYear <> MinYear && ISATLEVEL ( [Jahr] ), 

          [DVE]- CALCULATE ( [DVE], [Jahr] = CurrentYear -1 ),
          [DVE] - CALCULATE ( [DVE], [Variante] = "Basis" )
    )

     

    If you need to reuse this concept you need a regular measure, in that case refer to my previous post to provide some data

     

    If this helped, please consider giving kudos and mark as a solution

    mein replies or I'll lose your threadconsider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI

FBergamaschi's avatar
FBergamaschi
Super User
1 year ago

I think a visual calculation here would be the best approach anyway so I try to solve it immediately using the names in the visual picture you sent

 

Difference Visual CALC =
VAR MinYear = MINX ( ROWS, [Jahr] )

VAR CurrentYear = [Jahr]
RETURN

IF (
      [Variante] = "Basis" && CurrentYear <> MinYear && ISATLEVEL ( [Jahr] ), 

      [DVE]- CALCULATE ( [DVE], [Jahr] = CurrentYear -1 ),
      [DVE] - CALCULATE ( [DVE], [Variante] = "Basis" )
)

 

If you need to reuse this concept you need a regular measure, in that case refer to my previous post to provide some data

 

If this helped, please consider giving kudos and mark as a solution

mein replies or I'll lose your threadconsider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

SchmidMatt84's avatar
SchmidMatt84
New Member
1 year ago

Hi, thank you a lot. This worked well applied to a measure.

However, it was a bit more complicated with the field parameter. The only solution I found was this one. There are only 3 values, but its still a bit clumsy...

 

Wert Delta2 =
VAR _Variante = SELECTEDVALUE('10_Total_Merge'[Variante] )
VAR CurrentYear = SELECTEDVALUE( '10_Total_Merge'[Jahr] )
VAR MinYear = CALCULATE( MIN ( '10_Total_Merge'[Jahr] ), REMOVEFILTERS( ) )
VAR __SelectedValue =
    SELECTCOLUMNS (
        SUMMARIZE ( Auswahl_Kategorie, Auswahl_Kategorie[Auswahl_Kategorie], Auswahl_Kategorie[Auswahl_Kategorie Felder] ),
        Auswahl_Kategorie[Auswahl_Kategorie]
        )
RETURN
Switch(
    TRUE()
    ,__SelectedValue = "Pkm",
        IF (
            _Variante = "Basis" && CurrentYear <> MinYear,
            [Pkm] - CALCULATE ( [Pkm], '10_Total_Merge'[Jahr] = CurrentYear -1 ),
            [Pkm] - CALCULATE ( [Pkm], '10_Total_Merge'[Variante] = "Basis" ))
    ,__SelectedValue = "PVE",
        IF (
            _Variante = "Basis" && CurrentYear <> MinYear,
            [PVE] - CALCULATE ( [PVE], '10_Total_Merge'[Jahr] = CurrentYear -1 ),
            [PVE] - CALCULATE ( [PVE], '10_Total_Merge'[Variante] = "Basis" ))
    ,__SelectedValue = "Yield",
        IF (
            _Variante = "Basis" && CurrentYear <> MinYear,
            [Yield] - CALCULATE ( [Yield], '10_Total_Merge'[Jahr] = CurrentYear -1 ),
            [Yield] - CALCULATE ( [Yield], '10_Total_Merge'[Variante] = "Basis" )
))