Forum Discussion

kateryna's avatar
kateryna
Regular Visitor
9 years ago

Enter Data with Formulas

Hello Everybody!

 

I have just started exploring PBI world and need to solve the following task: add formula to a cell in a table, which was added with the help of "Enter Data" tool. The purpose is to create a calculated cell referring to another imported table.

 

If anyone has an idea how to do that, I would be very-very grateful!

 

Printscreen is below:

 

 

Need Help

5 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi kateryna,

     

    In Power BI Desktop, there is no option to add formula to a cell to create a calculated cell in a table.

     

    In your scenario, you should create new calculate column instead. You can do it with DAX under Modeling tab, or with Power Query(M) with Query Editor > Add Column tab. For more details about how to create calculated columns in Power BI Desktop, you can refer to this article.

     

    If you have any question about the formula to create the new column, just post your table structures with some sample data and your expected result here. :smileyhappy:

     

    Regards

    • kateryna's avatar
      kateryna
      Regular Visitor
      9 years ago

      Hello v-ljerr-msft!

      Thank you for replying to me! Got back from short vacation and just saw your advice.

      I am afraid that creating a calculated column would not be enough to complete the task. Let me give you a detailed example.

       

      1. I import data on Sales from SBO

       

      2. I created calculated column on estimated rebates to customers with the help of other related tables

       

       

      3. In the report I would like to see Estimated rebates Total in the rows as below:

       

       

       

      Is there any way to get this result?

       

      Very appreciate your ideas.

       

      Best regards,

         Kateryna

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi kateryna,

         

        I assume you already have a table called "Table1" like below.

        To get your expected result, you need to add a new table("Table2") with a single column like below(using Enter Data or Import it from Excel etc). 

        Then you should be able to use the formula below to create a new calculate column in Table2. :smileyhappy:

        Sales USD = 
IF (
    Table2[Customer] <> "Estimated Rebates",
    LOOKUPVALUE ( Table1[Sales USD], Table1[Customer], Table2[Customer] ),
    SUM ( Table1[Estimated Rebates USD] )
)

         

        Regards