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...
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 )

     

     

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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi afkl99,

     

    Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

     

     

    Best Regards!
    Dale