Enter Data with Formulas

[kateryna]

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:

[v-ljerr-msft]

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. 

Regards

[kateryna]

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]

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. 

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

Regards

[kateryna]

Dear @v-ljerr-msft,

Thank you for the details. I was almost sure it is solved, but here is where I am stuck:

Table 2 - this is example of visual report, which can be based on Customer, Product, Terms of Payment etc...so creating additional tables with vlookup will probably overload PBI.

What I have done - created a new table 2 with line Est Reb and created new calculated column Sales_SC. After I have appended table 2 with table 1, but the problem is that in column Sales_SC/ row Est Reb there is no values. I guess the problem is related to the fact that this is calculated column and not added one.

Is there any other way to incorporate somehow this additional line with calculated column into table 1?

Best regards,

  Kateryna

[v-ljerr-msft]

Hi @kateryna,

---

Table 2 - this is example of visual report, which can be based on Customer, Product, Terms of Payment etc...so creating additional tables with vlookup will probably overload PBI.

What I have done - created a new table 2 with line Est Reb and created new calculated column Sales_SC. After I have appended table 2 with table 1, but the problem is that in column Sales_SC/ row Est Reb there is no values. I guess the problem is related to the fact that this is calculated column and not added one.

---

Nice solution!

To make this solution work:

1. Created a new table 2 with line Est Reb and created new calculated column Sales USD, and another calculate column Estimated Rebates USD.

Sales USD = SUM(Table1[Estimated Rebates USD])

Estimated Rebates USD = SUM(Table1[Estimated Rebates USD])

2. Then you should be able to use UNION function to create a new calculate table "Table3" to join Table1 and Table2.

Table 3 = UNION(Table1,Table2)

Regards