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

[rush]

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

[v-yulgu-msft · Accepted solution]

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

[rush]

@v-yulgu-msft Thanks, the code works. Much appreciated.