Hi,
I'm trying the use the information given as the result of a calculation through a measure or calculated column to feed back into a dimension table, is there any smart way / best practice to do so?
I have a model where I'm retrieving information from different fact tables to make calculations.
1 table with facts (actuals)
1 table with budget (coming from a complete different data source than the actuals).
Based on my actual table, I could generate a product dimension table that actuals, budget (and other fact tables) connect to.
In order to get an actual vs budget, I created a measure, which is fine.
Now that I have a variance to budget calculated, what is the best way to categorise products being over budget as "over budget" and import this information back into my dimension table?
The best way I could find was doing some some of loop / redondance in the model:
1- create a second actual table, where each product exists only once a month, and use my measures of actuals and budget as calculated columns, so I can get the variance as a calculated column.
2- Then, for each row, I apply a simple if variance to budget < 0 then "over budget" otherwise "ok".
3- Finally, in my dimension table, I'm taking the last known result of the over budget category calculation.
This allows me to have the attribute in my dimension table, but I loose track of the historical information, as I'm only taking the latest known result to categorise a product as being over budget.
Another option is instead of step 3 above, I use the information straight out of the fact table... which means I end up with separate fact tables structured in a different way, which seems counterintuitive.
In the current state my model works, I'm just thiking if there's a smarter way to compute an result back into the model, or a "best practice" I could follow.
Thanks!
