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

Hi

Please include a small rows subset (not an image) of all the tables involved in your request, so that we can import them in Power BI and reproduce the data model (please show it).  Please include also the DAX you wrote so far and the result you get. In this way we can reproduce the scenario and help you. Thank you

  • 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

      Thanks, I've tried visual calculations as well. But that doesn't work in combination with field parameters? If there's a possibility to solve it with dax measures, that would be preferable...

      • FBergamaschi's avatar
        FBergamaschi
        Super User
        1 year ago

        Visual Calculation can work with Field Parameters but with some limitations, so it depends what you need to do. So I go back to the original reply:

        Please include a small rows subset (not an image) of all the tables involved in your request, so that I can import them in Power BI and reproduce the data model (please show it).  Please include also the DAX you wrote so far and the result you get. In this way we can reproduce the scenario and help you (Incude the field parameter usage you are considering: are you using a field parameters to allow users choose columns, measures, both?) Thank you

    • 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" )
      ))
    • FBergamaschi's avatar
      FBergamaschi
      Super User
      1 year ago

      My solution with measures

       

      Wert Value = SUM ('10_Total_Merge'[Wert] ) this is the measure I am referring
       
      Wert Delta =
      VAR _Variante = SELECTEDVALUE( '90_Version'[Variante] )
      VAR CurrentYear = SELECTEDVALUE( '90_Version'[Jahr] )
      VAR MinYear = CALCULATE( MIN ( '90_Version'[Jahr] ), REMOVEFILTERS( ) )
      RETURN
      IF (
            _Variante = "Basis" && CurrentYear <> MinYear && ISINSCOPE( '90_Version'[Jahr] ),
            [Wert Value]- CALCULATE ( [Wert Value], '90_Version'[Jahr] = CurrentYear - 1 ),
            [Wert Value] - CALCULATE ( [Wert Value], '90_Version'[Variante] = "Basis" )
      )
       

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

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

      Francesco Bergamaschi

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