Forum Discussion

bhmiller89's avatar
bhmiller89
Helper V
10 years ago

Calculating a Target Goal

 I have data where basically I calculate how many Target Hours are in a month.  Basically, any week day * 8hrs. I already wrote a column that numbers days of the week 1-7 where 1=Monday.

 

So I have this written: Target= 8* CALCULATE(DISTINCTCOUNT(DateTable[MasterDate]), FILTER(DateTable, DateTable[DayofWeek] <6))

 

However, I feel like the data being returned is not completely accurate. 

 

1. In my Table the Target doesn't sum a total for the number of people in the column.  The other columns of data show a sum at the bottom.  The yearly target for each person is 2088.  The Total at the bottom says "2088" and not "2088* count of users"

 

2. When I calculate a Utilization% I simply divided my measures: [Total Hours Entered]/ [Target] but this doens't seem to be returning correct data.

 

Any help or suggestions are appreciated

Need Help

2 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    10 years ago

    bhmiller89

     

    In this scenario, if there's no relationship between Employee table and Date table, your [Target] calculation will not aggregate on total row. You suppose to have a table about employee entering hours. I suggest you add a target hour column and give 8 for all rows. Then calculate the [Target] based on this "Logging Hours" table instead of Date table.

     

    Regards,

  • konstantinos's avatar
    konstantinos
    Memorable Member
    10 years ago

    bhmiller89 It would be better to have more information about your data, table.

     

    I think your calculation is wrong, since you just counting unique employers worked in working days multiply by 8

     

     

    Hope this is what you meant

    Target = 8 hours * No of Employees * Working Days of period

     

    Target =
8
    * CALCULATE (
        COUNTROWS ( DateTable );
        DateTable[DayOfWeek<6)
     * DISTINCTCOUNT(MasterTable[EmployeeID]
    )