Forum Discussion

afkl99's avatar
afkl99
Frequent Visitor
9 years ago
Solved

Dividing rows in table

Hi, 

I am new to power bi and am trying to transfer my r code into steps in the power bi query

  • The r code itterates through each row of the data frame and divides the value in row (a) by the value in row (a-1): value_a / value_(a-1)

 

I have a large table with a date and value as follows: 


I want to create a new column which takes the value of each row and divides it by the value by that of the pervious row -a seemingly easy task. i.e. the calculated column will be 0 for the first row and then 300/400 for the second row and 250/300 for the third row and so on.

 

I have spent hours looking for the solution for this and have come up empty as often the solutions uses functions that are not available in the query when creating custom column 

 

Thank you so much for your help! 

Need Help
  • CahabaData's avatar
    CahabaData
    9 years ago

    this may help get you on your path - it doesn't do the calculation but does bring in the value from the prior day/row:

    terms are generic - first is a calculated column approach:

     

    PreDaySessions_Column =

    VAR CurrentDay = Table1[Date]

    VAR PreDay =

     CALCULATE ( MAX ( Table1[Date] ), FILTER ( Table1, Table1[Date] < CurrentDay ) )

    RETURN

     LOOKUPVALUE ( Table1[SessionS], Table1[Date], PreDay )

     

    this is a Measure

    PreDaySessions_Measure =

    VAR PreDay =

      PREVIOUSDAY ( Table1[Date] )

    RETURN

     LOOKUPVALUE ( Table1[SessionS], Table1[Date], PreDay )

     

     

4 Replies

  • CahabaData's avatar
    CahabaData
    Memorable Member
    9 years ago

    this may help get you on your path - it doesn't do the calculation but does bring in the value from the prior day/row:

    terms are generic - first is a calculated column approach:

     

    PreDaySessions_Column =

    VAR CurrentDay = Table1[Date]

    VAR PreDay =

     CALCULATE ( MAX ( Table1[Date] ), FILTER ( Table1, Table1[Date] < CurrentDay ) )

    RETURN

     LOOKUPVALUE ( Table1[SessionS], Table1[Date], PreDay )

     

    this is a Measure

    PreDaySessions_Measure =

    VAR PreDay =

      PREVIOUSDAY ( Table1[Date] )

    RETURN

     LOOKUPVALUE ( Table1[SessionS], Table1[Date], PreDay )

     

     

    • afkl99's avatar
      afkl99
      Frequent Visitor
      9 years ago
      Thank you! I had tried the first method earlier and it didnt seem to work - but i will try it again 
       
      Can you add a measure when using the query? I was under the impression that it had to be done outside the query editor.  
      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        9 years ago

        Hi afkl99,

         

        We did it in the data view or report view. You also can try this formula.

         

        Column =
VAR currentDate = 'Table1'[Date]
VAR PreDate =
    CALCULATE (
        LASTDATE ( 'Table1'[Date] ),
        FILTER ( 'Table1', 'Table1'[Date] < currentDate )
    )
RETURN
    IF (
        ISBLANK ( PreDate ),
        0,
        DIVIDE (
            'Table1'[Value],
            LOOKUPVALUE ( 'Table1'[Value], Table1[Date], PreDate ),
            0
        )
    )

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        Best Regards!

        Dale