headcount per dept RRS feed

  • Question

  • I have a requirement where i have to calculate below:

    • budgeted cost per department per application per month
    • budgeted cost per department per application rolled up to quarter or year

    I these joins so far:

    • Employee * Headcount->1..M
    • Headcount*Usage-> M..M
    • Date*Headcount-> 1..M
    • Date*Budget->1..M

    • Usage table - contains one row per employee per Item.Oracle is allocated to only those 2 users but Salesforce is to be allocated to headcount based on month from the HC data provided.Usage table for now is not connected to anything.

    I have been provided with headcount file for 3 yrs until 2023.The headcount data contains one row per employee per month like the sample provided below.Flag column indicates whether employee is to be counted towards headcount that month or not.

    How do i build a measure that calculate cost per month roll up to quarter/year grouped by department/country?

    The above joins give me correct headcounts sliced by dept by creating a measure distinctcount('Usage'[username]) .I am struggling to have this headcount providing the cost breakouts per dept.Pls guide

    Wednesday, July 29, 2020 11:07 PM

All replies

  • What is the cost I 've not seen it in the pic,  is it the amount column?

    Does the flag affect the result of your desired measure?

    "cost per month roll up to quarter/year" are you needing a monthly cost column and a running total column?

    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 30, 2020 6:28 AM
  • Lukas,

    Amount from Budget table is the cost.

    Flag decides whether the user is included in headcount for that month or not.1 means to count it for a month and 0 means dont count.

    Cost per month roll upto quarter or year- i need to calculate the cost per dept per month if month is the slicer ,cost per dept per quarter when quarter is the slicer and cost per dept per year when year is the slier.(running)

    Thursday, July 30, 2020 11:14 AM