GraH - Guido
Well-Known Member
Hi all,
I'm not so active any-more on the forum since a few months because I'm very busy with Excel at work. Can't be motivated to do even more Excel in my free time. Seems I've missed out on some fun...
To the point now. I'm busy working on a data model that combines multiple tables; both dimension as fact tables to allow integrated project reporting. In my current model, as attached, I'm able to view budget and actuals side by side on "project level". Now users are asking to allow viewing the budget and actuals, but show the task level. On that granularity we do not have budget. Neither do I have a dimension table with tasks.
When I simply add the task level in the pivot, I get a Cartesian result as expected. I made an alternative model in which all the fact tables contain the task level. So if we have for project A say 3 tasks in the actuals, then during my ETL process I split the budget in 3 lines, one for each task and divide the given budget amount by 3 as well. The downside here: users get the impression there is a budget on task level. That's never the case.
Would anyone have some idea on how to do this? How do I need to update my model? Or would you have the solution to write a correct DAX that allows viewing the actuals on task level, but only shows budget on project level, even if the "dummy" tasks are present in the budget fact. (This alternative model is not in the attachment).
Looking forward to your guidance.
G.
I'm not so active any-more on the forum since a few months because I'm very busy with Excel at work. Can't be motivated to do even more Excel in my free time. Seems I've missed out on some fun...
To the point now. I'm busy working on a data model that combines multiple tables; both dimension as fact tables to allow integrated project reporting. In my current model, as attached, I'm able to view budget and actuals side by side on "project level". Now users are asking to allow viewing the budget and actuals, but show the task level. On that granularity we do not have budget. Neither do I have a dimension table with tasks.
When I simply add the task level in the pivot, I get a Cartesian result as expected. I made an alternative model in which all the fact tables contain the task level. So if we have for project A say 3 tasks in the actuals, then during my ETL process I split the budget in 3 lines, one for each task and divide the given budget amount by 3 as well. The downside here: users get the impression there is a budget on task level. That's never the case.
Would anyone have some idea on how to do this? How do I need to update my model? Or would you have the solution to write a correct DAX that allows viewing the actuals on task level, but only shows budget on project level, even if the "dummy" tasks are present in the budget fact. (This alternative model is not in the attachment).
Looking forward to your guidance.
G.