Forum Discussion
LOOKUPVALUE
Dear Community,
As I have two tables, DB and ActualProfit, I would like to look up the sum of DB table based on month and year into ActualProfit table, but Do not replace the numbers in Actual Profit that are already filled up.
This is Actual Profit table.
This is DB table.
I would like to sum the value of months February and March in the DB table then look into the Actual Profit table.
Knowing that this code can look up the total profit, but will replace the actual profit that has been filled
lookup = CALCULATE(SUM('DB'[Profit]),FILTER('DB','DB'[Month]= ActualProfit[Month] && 'DB'[Year] = ActualProfit[Year] ))
Expectation:
Any help will be greatly appreciated.
Attached with the pbix: https://drive.google.com/file/d/1x-snBm9WxQl8esw8Mz1AB4JekGj41Za5/view?usp=sharing
Hey NickProp28 ,
You can try something like this:
lookup = if(ActualProfit[Profit]<> BLANK(),ActualProfit[Profit],CALCULATE(SUM('DB'[Profit]),FILTER('DB','DB'[Month]= ActualProfit[Month] && 'DB'[Year] = ActualProfit[Year] )))This will create a new column taking values from the Profit if they are already there and if not, it will do the calculation and fill the result. The outcome will be as:
Will this solve the purpose?
2 Replies
Hey NickProp28 ,
You can try something like this:
lookup = if(ActualProfit[Profit]<> BLANK(),ActualProfit[Profit],CALCULATE(SUM('DB'[Profit]),FILTER('DB','DB'[Month]= ActualProfit[Month] && 'DB'[Year] = ActualProfit[Year] )))This will create a new column taking values from the Profit if they are already there and if not, it will do the calculation and fill the result. The outcome will be as:
Will this solve the purpose?
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new column.
lookup = VAR _condition = ISBLANK ( ActualProfit[Profit] ) RETURN CALCULATE ( SUM ( 'DB'[Profit] ), FILTER ( 'DB', 'DB'[Month] = ActualProfit[Month] && 'DB'[Year] = ActualProfit[Year] ) ) * _condition + ActualProfit[Profit]
