Some help on time intelligence please

[jujiro-eb]

I have created a simplified scenario mimicking my original issue.  The model contains the following main data sources.

Dept: Table containing list of departments

Emp: Table containing list of employees

Emp salary history as of 1st of every year

DeptBudget: Budgets for each department as of 1st of every year.  This dataset was introduced only in 2020, so it does not have 2019 data.

On the visual, I want to show Dept Id, Name, it's budget, sum of this year's salaries, sum of last year's salaries, and the difference in salaries between this year and the prior.

The visual did not work as expected. It was missing last year's salaries data.
It worked when I remove the budget column from the visual.  

Okay,  I do not have any budgets for 2019 for any of the departments  as result all last year salaries are being returned as Blank.  So, I went ahead and added the 2019 budgets as well.  This brings back last year salaries except for one department.  Even though, this department (100) has budgets for both 2019 and 2020.

The entire solution can be downloaded from this link.

My questions:

Why the last year's salary totals are not showing for department 100?
What is the best way to tackle the missing budgets to avoid messing up time intelligence calculations?

Thanks.

[Pragati11]

Hi @jujiro-eb ,

I have done one modification in your relationships. I have made them all bi-directional as follows:

Then in the table visual do the following against "Budget" column:

This resolves the issue.

Kindly mark this solution as resolved.

Thanks,

Pragati

[jujiro-eb]

Hi Pragati,

In your solution, Dept 500 with some budget numbers is not appearing.  Also, if you change the Budget to "Don't Summarize," you will see that the numbers are off.

Thanks.

[PaulDBrown · Accepted solution]

@jujiro-eb 

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.

[jujiro-eb]

Hi Paul,

Thank you.  You have answered my question.  I was stuck on using "Don't summarize," for the budget numbers, as they are actually percentages and I did not want to sum them up, which would not make any sense.  However, your suggestion, "you can get rid of the total using ISINSCOPE to show BLANK," will work fine for me.

Thanks again.

[Pragati11]

Hi @jujiro-eb ,

DEPT = 500 is not appearing in the table because "EmpDataFact" table hasn't got rows for Dept = 500.

Thanks,

Pragati

[PaulDBrown]

@jujiro-eb 

Change the model as follows: 

1) Delete the relationship between your budget table and the EmpDatafact table.
2) Now create relationships between your Dept table and Date table to your Budget table.

this way your Dept table and Date table become the lookup tables for both your Emp data table and you budget table. Use these lookup tables as slicers, filters and in filter expressions in your measures

[jujiro-eb]

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.

[PaulDBrown]

@jujiro-eb 

any chance you can share some dummy data from both emp and budget tables?

or the PBIX file istelf if possible

[jujiro-eb]

Sure.  You can get everything you need from this link.

[v-alq-msft]

Hi, @jujiro-eb 

With the way of @PaulDBrown , I modify the relationships as follows.

Result:

Best Regards

Allan

[jujiro-eb]

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.

[jujiro-eb]

Thanks Paul.  So, I did what you had suggested.

I am still missing last year's numbers for department 100.

The measure for last year's salary is as following:

Tot Salaries LY = CALCULATE([Tot Salaries],SAMEPERIODLASTYEAR('Date'[Date]))