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...
Need Help
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