cumulative target line

Question

&nbsp;&nbsp;We have a metric for employees to complete 5 of a specific report type every year.&nbsp; I need to create a line for target number of reports throughout the year, but need it to be cumulative, so that we can compare if our rate is on track with target.&nbsp; Something like the picture below.&nbsp; &nbsp;&nbsp; The target line to be dynamic, so that we have new employees start, or employees leave, the target number will adjust accordingly, and we want to be able to filter by manager and have that target move, too.&nbsp; How can I do this?&nbsp;I have the following tables&nbsp;Employee TableEmployee ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Manager IDAAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 111BBB&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;222&nbsp;Report TableReport Number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Employee ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Date Closed001&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1/2/19002&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BBB&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1/9/19&nbsp;and a date dimension table.&nbsp;The Employee table is linked to the Report table by the Employee ID, and the Report table is linked to the Date Dimension table by the Date Closed.&nbsp;&nbsp;What I've tried to far is to make a calculated measure in the employee table to count all full time employees (filtering out part time, etc).&nbsp; Then I was going to create a new column on the date dimension table to calculate the number of reports needed per day of the year (rate='employee'[# full time]*5/365).&nbsp; Then create a measure on the date table&nbsp; to calculate the cumulative year to date of that target rate to get a straight increasing line on the chart.&nbsp; But when I try to do this instead of getting a constant number on each row of the date table, it's giving me much smaller numbers that aren't constant, and are blank on weekend days.&nbsp; Like below.&nbsp;Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Rate='employee'[# full time]*5/3651/1&nbsp;/19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1/2&nbsp;/19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.071/3&nbsp;/19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.051/4/19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.031/5/191/6/191/7/19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.081/8/19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.03

mfelix · Answer

Hi&nbsp;toniacheung&nbsp;,
&nbsp;
You need to follow the steps below:
&nbsp;

Create a day number column on the Dim date table:

&nbsp;
DAY of year =
DATEDIFF ( DATE ( YEAR ( DateDim[Date] ); 1; 1 ); DateDim[Date]; DAY ) + 1
&nbsp;

Add the following measure to your model:

&nbsp;
&nbsp;
&nbsp;
Cumulative Target =
CALCULATE (
    SUMX ( DateDim; 5 / 365 )
        * MAX ( DateDim[DAY of year] )
        * DISTINCTCOUNT ( Employe[Employee ID] )
)
&nbsp;
Be aware that this model needs to have the date on your x-axis.
&nbsp;
I'm making the distinct count of the employee ID however in your description you refer that you need this to be dynamic probably you need to make some adjustment on the distinct count to make it also interact with the dates.
&nbsp;
Regards,
MFelix
&nbsp;

toniacheung · Answer

I was able to get most of what I want.&nbsp; I&nbsp;&nbsp;created a calculated column in the Employees table (#Employees=DISTINCTCOUNT([Employee ID]).&nbsp;&nbsp;Then I set up a calculated column in the DateDim table to get the daily target rateRate=MAX('Employees'[#Employees])*5/365.&nbsp; This gets me a column on the date dim table that has a constant value.&nbsp; Then I have the calculated measure Goal YTD=TOTALYTD(SUM('DateDim'[Rate]), 'DateDim'[Date]).&nbsp;But when I filter by employee or their manager, the calculated #Employees column changes accordingly, but the calculated Rate column (which uses the #Employees in its calculation) doesn't change.&nbsp; How do I get the rate column to update based the filtered #Employees?

cmcmahan · Answer

#Employees should be a measure, not a calculated column.&nbsp; Calculated columns are only re-calculated on data refresh. Measures are re-evaluated every time they are used.

toniacheung · Answer

&nbsp;&nbsp;Okay.&nbsp; When I do the # employees as a measure, my calucation for the Rate column (Rate=MAX('Employees'[#employees calc])*5/365) breaks and I get a message saying that it can't be used in this expression.&nbsp; If I take out the MAX function, instead of having a constant rate for each day, Power BI is dividing what should be the total for 1 day (~7) over 365 days, and the value is not constant for every day (screenshot below).&nbsp; If I take the "/365" out of that formula, it's still giving me inconsistent numbers throughout that column.&nbsp; And it's still not affected by filtering by employee.&nbsp;&nbsp;&nbsp;

mfelix · Answer

Hi&nbsp;toniacheung&nbsp;,
&nbsp;
Did you try to use my measure as I posted before? On the tests I did the cumulative when ok.
&nbsp;
Regards,
MFelix

Forum Discussion

cumulative target line

7 Replies

Related content

Cumulative Line Target in Chart

Cumulative Total

Cumulative Line Formula

Cumulative Daily Target

Target Line