Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Help to customize table using existing data and calculations

Dear Power BI 🙂


I have the following data table (inserted below) I need to convert into a new table:

 

 

So essentially, I receive values for cars at given ages: Newbuild, 5, 10 and 15 years old. 
From that data I need to make a table that show the value of a car at any given age. I need to fill in the values between newbuild and 5 year old and 10 years old etc. The way the numbers need to be filled in is by a linear depreciation. So if a Ford newbuild is worth 45 and a 5 years old Ford is worth 40, then the car have decreased by 5 over 5 years and thus 1 over 1 year. I think I have made the calculations clear in the picture. 

My problem is that I have no Idea to make such a table in Power BI. The picture is from Excel, where this seems to be easy to do. 

Can anyone help me?

Sincerely, Signe 



Date (value evaluation)CarValue typeValue
3/2/2023FordNew45
3/2/2023Ford5 year old40
3/2/2023Ford10 year old30
3/2/2023Ford15 year old20
3/2/2023VWNew35
3/2/2023VW5 year old30
3/2/2023VW10 year old20
3/2/2023VW15 year old10
3/2/2023FiatNew28
3/2/2023Fiat5 year old25
3/2/2023Fiat10 year old20
3/2/2023Fiat15 year old15
Need Help

1 Reply

  • MAwwad's avatar
    MAwwad
    Solution Sage
    3 years ago

     

     

    You can create the desired table in Power BI by using the following steps:

    1. Create a new table by going to "Modeling" > "New Table" in the ribbon.
    2. Use the following DAX formula to create the table:

       

       
      Car Value = VAR BaseTable = SELECTCOLUMNS('OriginalTable', "Car", [Car], "Value type", [Value type], "Value", [Value]) VAR NewTable = UNION(BaseTable, SELECTCOLUMNS(BaseTable, "Value type", "Newbuild", "Value", 0)) RETURN ADDCOLUMNS(NewTable, "Date", TODAY())
       

      In this formula, replace 'OriginalTable' with the name of the original table that contains the data you provided. This formula creates a new table called "Car Value" that has one row for each combination of Car and Value type. The Value column is filled in using a linear interpolation between the available values.

      1. Create a new matrix visual and add "Car" to the Rows field, "Date" to the Columns field, and "Value type" to the Values field.
      2. In the "Values" field, click on the dropdown arrow and select "Don't summarize". This will ensure that each row in the matrix shows the actual value from the "Car Value" table.

        I hope this helps! Let me know if you have any questions.