Forum Discussion

rush's avatar
rush
Icon for Helper V rankHelper V
9 years ago
Solved

Sum Billable Hours (Numeric) per Year-Month (Data type = Text) then DIVIDE by a specific Number?

I would like to know how to sum up all my Billable Hours (Numeric) per Year-Month (Data type = Text) then DIVIDE that by the No. of Working Days (Numeric) in that month.

 

e.g. Total billable hours for 16-01 = 480 THEN DIVIDE by No. of Working Days for that Year-Month being 20 = 24

 

I have 3 coumns of data:

 

Billable Hours         Year-Month       No. of Working Days

160                         16-01                   20

160                         16-01                   20

8                             16-02                   21

8                             17-01                   22

160                         16-01                   20

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi rush,

     

    If you want to add an extra column in above table to show the expected result, please create a calculated column referring to this formula:

    Result =
DIVIDE (
    CALCULATE (
        SUM ( 'Sum Bill'[Billable Hours] ),
        ALLEXCEPT ( 'Sum Bill', 'Sum Bill'[Year-Month] )
    ),
    'Sum Bill'[No.of working days]
)

    Best regards,
    Yuliana Gu

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi rush,

     

    If you want to add an extra column in above table to show the expected result, please create a calculated column referring to this formula:

    Result =
DIVIDE (
    CALCULATE (
        SUM ( 'Sum Bill'[Billable Hours] ),
        ALLEXCEPT ( 'Sum Bill', 'Sum Bill'[Year-Month] )
    ),
    'Sum Bill'[No.of working days]
)

    Best regards,
    Yuliana Gu