Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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 |
---|---|
98 | |
91 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |