Forum Discussion
Some help on time intelligence please
You need to set up the model as I stated to ensure all values for your Dept ID are included in the table visual:
If you check the DeptId values in the EmpDataFact, Id 500 is missing. There are no rows for this ID in this table (hence the sum of salaries is BLANK). If you use the DeptId field from the EmpDataFact, you will not see data for ID 500.
By establishing the structure as above, and use the fields from the Dept Table in your visuals, you enure that all IDs are present
As regards the values, you need to have an aggregation (SUM, DIVIDE, MAX, MIN...)
If you work with % values, it is probably a good idea to calculate the absolute values deriving from the % for aggregation purposes (In my book, a % in a row of a data table should be used to calculate a value in that row; for final results I would do the % calculation at the end after aggregations are done and over with).
If your values are percentages and ONLY HAVE ONE row per ID (per date period you are calculating against) related to this %, then you can use any of these aggregations (The total as a result is irrelevant since it is not a calcualtion - unless you want something specific shown- you can get rid of the total using ISINSCOPE to show BLANK). If you have more than one row per ID (per date period you are calculating against) as a percentage, you will either have to make calculations prior to aggregating (recommended) or choose which % you wish to be seen/used.
I have used SUM for both salaries and budget amounts, and this is what your data returns.
One more thing. Your solution is also creating the exact same behavior as my original one. The last year's salary figures completely disappear if I do not have any budget data for 2019. I think I switched to my design my removing budgets to link with dept and date dimensions, just for this reason.
any chance you can share some dummy data from both emp and budget tables?
or the PBIX file istelf if possible
Hi, jujiro-eb
With the way of PaulDBrown , I modify the relationships as follows.
Result:
Best Regards
Allan
Hi Allan,
Try changing the Budget# to "Don't summarize." You will be abke to reproduce the behavior I am observing. In my actual data, the budget# is actually a percent, so it cannot be summed.
Thanks.
