Forum Discussion

toniacheung's avatar
toniacheung
Frequent Visitor
7 years ago

cumulative target line

    We have a metric for employees to complete 5 of a specific report type every year.  I need to create a line for target number of reports throughout the year, but need it to be cumulative, so th...
Need Help
MFelix's avatar
MFelix
Icon for Super User rankSuper User
7 years ago

Hi toniacheung ,

 

You need to follow the steps below:

 

  • Create a day number column on the Dim date table:

 

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

 

  • Add the following measure to your model:

 

 

 

Cumulative Target =
CALCULATE (
    SUMX ( DateDim; 5 / 365 )
        * MAX ( DateDim[DAY of year] )
        * DISTINCTCOUNT ( Employe[Employee ID] )
)

 

Be aware that this model needs to have the date on your x-axis.

 

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.

 

Regards,

MFelix

 

  • toniacheung's avatar
    toniacheung
    Frequent Visitor
    7 years ago

    I was able to get most of what I want.  I  created a calculated column in the Employees table (

    #Employees=DISTINCTCOUNT([Employee ID]).  

    Then I set up a calculated column in the DateDim table to get the daily target rate

    Rate=MAX('Employees'[#Employees])*5/365.  This gets me a column on the date dim table that has a constant value.  Then I have the calculated measure Goal YTD=TOTALYTD(SUM('DateDim'[Rate]), 'DateDim'[Date]).

     

    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.  How do I get the rate column to update based the filtered #Employees?

    • Cmcmahan's avatar
      Cmcmahan
      Icon for Resident Rockstar rankResident Rockstar
      7 years ago

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

      • toniacheung's avatar
        toniacheung
        Frequent Visitor
        7 years ago

         

         

        Okay.  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.  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).  If I take the "/365" out of that formula, it's still giving me inconsistent numbers throughout that column.  And it's still not affected by filtering by employee.