String Days and Hours, monthly

[KimNixon]

Hi,

I have a column which reports time worked in hours (per id/row).  This I can report on monthly and I can calculate monthly average.  However I am converting the hours using DAX to display Days & Hours 

String NewColumn =

var vHours=ROUND([Columnwithhours],0)

var vDays=int( vHours/24)

var vRemainingHours=MOD(vHours, 24)

return

vDays&" Days & "& vRemainingHours& " Hours"

So the final output is something that looks like......5 days 4 hours.  

However, I need to report with this 'NewColumn' containing days & hours at a monthly level, Average time worked per month.  When I include the new column in my table it reports per ID per month, something like this

	Hours worked	Days & Hours
Jan	12	0 days 12 hours
Jan	22	0 days 22 hours
Jan	10	0 days 10 hours

The final output I require would be 

	Ave hours worked	Days & Hours
Jan	48	2 days 0 hours
Feb	55	2 days 7 hours
Mar	50	2 days 2 hours

Any help would be much appreciated 

[Anonymous]

HI @KimNixon ,

You can try to use the following measure formula to calculate the average of work hour based on month and convert them to time duration string:

Measure =
VAR _avg =
    CALCULATE ( AVERAGE ( 'Table'[Hour] ), VALUES ( 'Table'[Date].[MonthNo] ) )
VAR _rand =
    ROUNDDOWN ( _avg, 0 )
RETURN
    IF (
        _avg >= 24,
        INT ( _rand / 24 ) & " day"
            & MOD ( _rand, 24 ) & " hours",
        _rand & " hours"
    )

Regards,

Xiaoxin Sheng

[amitchandak]

create an hourly measure. Create an avg of the sum.

Refer - this is the sum of Avg

https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

Then format this column to show final result in day format

[KimNixon]

Thank you for the steer, I am getting a circular reference error, I must be going wrong somewhere