Forum Discussion

mariomyhanh's avatar
mariomyhanh
Icon for Helper I rankHelper I
3 years ago
Solved

Headcount growth rate

Good afternoon, 

 

I'm trying to figure out a dax to calculate the growth % year over year for each dept  with the sample query below.

 

I created a clustered column chart to show # of distinct position each year by department using a  query with similar headings below.

 

I would like to create a formula to show growth % year over year within each dept when hovering over certain year (sample graph i would like to show below).  Please help.  Thank you

 

 

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I think my sample model is simpler than yours.

    In order to get previous year's numbers in your model, please try using relevant column from Dim Date table.

    Thanks.

7 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

    Position # count: = 
COUNTROWS( VALUES(Data[Position #]) )

     

    Position # count YoY: =
VAR _currentyear = [Position # count:]
VAR _prevyear =
    CALCULATE ( [Position # count:], FY[FY] = MAX ( FY[FY] ) - 1 )
RETURN
    DIVIDE ( _currentyear - _prevyear, _prevyear )
    • mariomyhanh's avatar
      mariomyhanh
      Icon for Helper I rankHelper I
      3 years ago

      Is there a way to count position # using distinct count? for example, if there's a repeated position number in the same fiscal year to only count it once. I tried this forumula

      DistinctCountofPosition = DISTINCTCOUNT('Staffing Roster'[Position])
       

      and it works in a table, but when i try to add it to the dax provided for YoY% it will not work.

      Position # count YoY: =
      VAR _currentyear = [DistinctCountofPosition]
      VAR _prevyear =
          CALCULATE ( [DistinctCountofPosition], FY[FY]= MAX ( FY[FY] - 1 )
      RETURN
          DIVIDE ( _currentyear - _prevyear, _prevyear )

      please help

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        3 years ago

        Hi,

        thank you for your message.

        I am not sure but please check if you missed to insert  ")" like the below.

         

        Position # count YoY: =
VAR _currentyear = [DistinctCountofPosition]
VAR _prevyear =
    CALCULATE ( [DistinctCountofPosition], FY[FY] = MAX ( FY[FY] ) - 1 )
RETURN
    DIVIDE ( _currentyear - _prevyear, _prevyear )

         

        Or, please share your sample pbix file's link, and then I can try to look into it to come up with a more accurate solution.

        Thanks.