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 )

     

     

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

      • 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