Forum Discussion
Enter Data with Formulas
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
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
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