Forum Discussion
Targets/Goals best practice
Hi everyone,
I have created a dashboard with multiple KPIs, such as on-time performance and failure rate. Now, I need to show actuals vs. targets for these KPIs. The issue is that the targets exist at different levels.
For example, there are goals defined for region-to-region and region-to-worldwide combinations by product. Some examples include: US to Canada by product, Canada to worldwide (all regions) by product, and Canada to China or Japan by product. This creates multiple combinations of region/market-level goals.
One approach I considered is merging the static goals into the fact table using Power Query by creating composite keys. This seems to work, but I’d like to know if there’s a better approach. The goal is that when a user selects a specific combination—such as origin country/region, destination country/region, and a product—the dashboard should display the correct target.
Additionally, there are targets defined at both the quarterly and annual levels. If a user views data for a particular week, the dashboard should display the corresponding quarterly target.
Please let me know your thoughts.
Hi MaxBell310
Another way to handle this, is to move the matching logic into the data model itself. You can create a small bridge (combination) table that lists all valid Origin–Destination–Product combinations and assign a key to each one. Then connect both your actuals and your targets to this table. That way, when a user makes a selection, the model naturally brings back the correct target without needing complex calculations.
For things like Worldwide targets, you can just include those as rows in the same table and map your data accordingly in Power Query. And for the time mismatch (quarterly/annual vs weekly), you can either spread targets to a lower level or connect them using a common time key like Year or Quarter. Overall, this keeps things simpler, easier to debug, and more stable compared to merging everything or relying too much on DAX.
If there are any deviations from your expectation please let us know we are happy to address.
Thanks.
6 Replies
If I understand your question well, I think it is very similar to the DAX patterns solution to budgeting on different granularities as explained in detail here: https://www.daxpatterns.com/budget/
The problem you are describing has quite some moving parts to consider, which I think are well documented in the pattern and thus it is a bit easier to refer to that than to detail it out here completely.
I will go through this. Thank you.
Hi MaxBell310
Thank you for reaching out to the Microsoft Fabric Forum Community.
Kobes Thanks for the inputs.
I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.
Any other suggestions?
Hi MaxBell310
Another way to handle this, is to move the matching logic into the data model itself. You can create a small bridge (combination) table that lists all valid Origin–Destination–Product combinations and assign a key to each one. Then connect both your actuals and your targets to this table. That way, when a user makes a selection, the model naturally brings back the correct target without needing complex calculations.
For things like Worldwide targets, you can just include those as rows in the same table and map your data accordingly in Power Query. And for the time mismatch (quarterly/annual vs weekly), you can either spread targets to a lower level or connect them using a common time key like Year or Quarter. Overall, this keeps things simpler, easier to debug, and more stable compared to merging everything or relying too much on DAX.
If there are any deviations from your expectation please let us know we are happy to address.
Thanks.
Thanks for the suggestions.
