Forum Discussion

Fro88er's avatar
Fro88er
Icon for Helper IV rankHelper IV
4 years ago
Solved

Cumulative Total of Average Employee Ramp

I am trying to visualize the Cumulative Total of Average Employee Ramp, using invidudual measures M1, M2, etc..

 

Result:

The reason I have individual monthly measure(s): [1M Avg] is that I need to Sum the 1M 0-30 then also count the number of employees that started in that month usng Tenure-month.  This is important to remove any new employees that weren't active yet.  For example, Month [6M Avg] sums the 6 [6M 151-180] production, and divided the number of employees, removing anyone with a tenure less than 5 months.  I can't count employees 6 months-in if they weren't employed yet, during that time.

 

6M Avg =
CALCULATE(DIVIDE(SUM('DimEmployeeHR'[6M 151-180]), [Employee Cnt]),
FILTER('DimEmployeeHR', 'DimEmployeeHR'[Tenure - Month] >= 6)
)

 

NOW, that I have the averages by month using individual measure(s) how can I cumulative total each measure?  Do I need to create a summarized table? The frequencey will only be monthly.  Huge shoutout to whoever can solve this! Thank you

 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    I really don't think you need all these separate measures for each month if you rework things a bit.

     

    Given that you do, a cumulative total of multiple measures is going to be ugly.

    Cumulative M =
SWITCH (
    SELECTEDVALUE ( DimDate[Month] ),
    1, [1M],
    2, [1M] + [2M],
    3, [1M] + [2M] + [3M],
    4, [1M] + [2M] + [3M] + [4M],
    [et cetera]
)

     

     

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    I really don't think you need all these separate measures for each month if you rework things a bit.

     

    Given that you do, a cumulative total of multiple measures is going to be ugly.

    Cumulative M =
SWITCH (
    SELECTEDVALUE ( DimDate[Month] ),
    1, [1M],
    2, [1M] + [2M],
    3, [1M] + [2M] + [3M],
    4, [1M] + [2M] + [3M] + [4M],
    [et cetera]
)

     

     

    • Fro88er's avatar
      Fro88er
      Icon for Helper IV rankHelper IV
      4 years ago

      Unfortunatly, the AVG measures are referencing a Temp/Perm GP calc column by using Upper/lower date HireDate plus 30 days, 31 to 60, 61 to 90, etc, etc. There is not ties to a calendar because my first month production say hired on 1/1/2021 is different than someone hired on 6/1/2021.  This would be month 6 production for the 1/1/2021 new hire. Ramp is tricky I know.

       

      Since I have the averages, can a create a virtual table, and tie each measure to a number, 1, 2, 3, 4, .. then do your selectedvalue? Thoughts?

       

      1M 0-30 =

      VAR _EmpID = 'DimEmployeeHR'[EMPLOYEE_ID]

      VAR _lowerdate = 'DimEmployeeHR'[Hire_Date_Mod]

      VAR _upperdate = 'DimEmployeeHR'[Hire_Date_Mod] + 30

      Return

      [Perm GP](FILTER(FactPlacements, FactPlacements[EmployeeID] = _EmpID && FactPlacements[dateAdded] >= _lowerdate && FactPlacements[dateAdded] <= _upperdate)

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

        Yeah, use whatever column you were planning to use for the x-axis on your line chart instead of DimDate[Month]. If you need to create a new table, that's fine.

         

        I'm confident there's a better option than creating so many columns and measures but it might take significant model changes which are hard to do without having an actual pbix file to tinker with.