Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rush
Helper V
Helper V

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

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.