Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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)
Try this:
Correct this: SUM(('FinancialReportingGrossProfit'[Saldo])-('OKRTargetValue'[Gross Profit Target 2023]))
With This: SUM('FinancialReportingGrossProfit'[Saldo]) -FIRSTNONBLANKVALUE('OKRTargetValue'[Gross Profit Target 2023],1)
Thank you! Did not know that FIRSTNONBLANKVALUE function existed.
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |