Forum Discussion
DAX & Table Query Best Practice
Hi Byzza - You're on the right track with the idea of merging tables and unpivoting. Here’s a best-practice design to simplify modeling and comparison across all sources.
In Power Query:
Import the wide table
Use “Unpivot Columns” to transform all value columns (like Volume, Revenue…) into rows under Item + Value
Add columns: Source, Phasing, and any necessary metadata.You can write one dynamic DAX measure that works across all items (using SELECTEDVALUE or slicers)
Actual vs Budget Variance %
Variance % =
VAR Actual =
CALCULATE([Total Value], FactTable[Source] = "Actuals")
VAR Budget =
CALCULATE([Total Value], FactTable[Source] = "Budget")
RETURN
DIVIDE(Actual - Budget, Budget)Hope the above suggestion and details helps.
Hi Byzza
In your case, the easiest and most scalable approach is to put Budget, Actuals, Forecast, etc. into one fact table instead of keeping them separate. Structure it in a long format where each row has:
-
Date
-
Source (Actuals, Budget, Forecast)
-
Phasing (Week, Month)
-
Measure Type (Revenue, Volume, COGS)
-
Value
You can do this in Power Query by appending the tables and unpivoting the columns.
Once the data is in this format, you only need a single set of DAX measures. You can then use slicers or calculation groups (via Tabular Editor) to switch between Actuals vs Budget, Week vs Month, or even create dynamic variance calculations (Actual vs Budget, Actual vs Forecast, etc.) without hard-coding.
This way the model stays flexible, easier to maintain, and future-proof if you add new measures or data, you just add rows instead of redesigning everything.