How to create a calculated field with a single value in another table [field with single record]

[jobusania]

Hi All,

Im kinda new to PowerBI, and seem to be struggling with using RELATED and/or LOOKUP formula's for (what seems) a simple calculation.

At our company we are currently creating dashboards to see how well we are performing based on our defined KPI's. We want to compare the actual data with a "hard value" target for that year. I created a sharepoint online list with 1 record that shows targets for the year (in this case 'Target 2023) with columns specifying for which KPI. I imported the table "OKR Target Values" in PowerBI and all is fine.

What i would like to create is a calculated field, called "DeltaTarget" where i deduct the actual values with the OKR Target value (Actual - target = delta).

I have a column in 1 table called "Saldo" of which the sum is our Gross Profit of 2023. This column is in a table called "FinancialReportingGrossProfit". It looks like this (a bunch of the first rows with products sold at 13,75 euro's):

What i would like to do (semanticly speaking) is create a formula like this: DeltaTargetGrossProfit = SUM(('FinancialReportingGrossProfit'[Saldo])-('OKRTargetValue'[Gross Profit Target 2023]))

I run in to two challenges:

1) - [Gross Profit Target 2023] is in another table as [Saldo]. The tables do not have a relation. I figured i should use a LOOKUP function for this. But cant get this to work.

2) - The column [Gross Profit Target 2023] is a column with a single record. Is it possible to deduct a field with a single row from a sum of a field with multiple rows?

unfortunately i have little SQL & DAX Formula knowledge.. and cannot get this thing to work in PowerBI. Eventhough it seems a very simple formula (when you would create this in MS Excel for example). 

If you would like to assist me on this, i would be very happy and blessed. Thank you in advanced.

[Bibiano_Geraldo · Accepted solution]

Try this:
Correct this: SUM(('FinancialReportingGrossProfit'[Saldo])-('OKRTargetValue'[Gross Profit Target 2023]))
With This: SUM('FinancialReportingGrossProfit'[Saldo]) -FIRSTNONBLANKVALUE('OKRTargetValue'[Gross Profit Target 2023],1)

[jobusania]

Thank you! Did not know that FIRSTNONBLANKVALUE function existed.