Powerpivot Calculation column
Results 1 to 2 of 2

Thread: Powerpivot Calculation column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2014
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Powerpivot Calculation column


    I have two tables in powerpivot and I need a calculation formula.

    Im new with the PowerPivot world so please be gentle with me.

    My data looks like that:

    uniq order number | order price
    1 | 500$
    2 | 300$
    3 | 100$
    4 | 250&

    order number | Time
    3 | 1.5
    3 | 2
    1 | 5.2
    2 | 2.33
    1 | 4.5
    4 | 1.22
    3 | 1

    I want to add a calculation column to table 2 that calculat the price for the relative record.
    for example: the first record in table 2 sould return 22.2222

    My calculation:
    100/(1.5+2+1)*(Time column - first row)

    * The "Time" column is the "time*24" (example: 02:30*24 = 2.5)

    Thank you!

  2. #2
    MrExcel MVP
    Join Date
    Dec 2014
    Sydney, Australia
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Re: Powerpivot Calculation column

    You should not write a calculated column. Read my article here Calculated Columns vs Measures in DAX - Excelerator BI

    Create a third table that contains all the unique order numbers. Join the 2 tables you have already to the new table. Create a pivot table and place the order number from the new table in the pivot on rows.

    I can't understand the logic of your formula as written, but you can write a measure something like this

    =sum(table2[time]) *max(table1[order price])
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts