topic Re: Total & Average Hours in DAX Commands and Tips

Total & Average Hours

ringovski1 — Wed, 18 Aug 2021 03:29:26 GMT

Hi All,

I am trying work out the total average hours per request, a request can have 1 to x phases(rows). I have correctly calcucated the duration Hours & Weeks though the AVG & SUM hours are incorrect.

Measures:

Start Date = MINX(

KEEPFILTERS(VALUES('boardingdata',[taskstarted])),

CALCULATE(MIN('boardingdata',[taskstarted]))

Duration HR = DATEDIFF([StartDate],NOW(),Hour)

Duration WK = DATEDIFF([StartDate],NOW(),Week)

AVG Duration HRS =DIVIDE([Duration HR], count(boardingdata[PhaseTxt]))

AVG Duration HRS is wrong it should be (116+1127+115+166+188+187) =1899/6 = 316.6 average hours for that request num for each phase/row. Hours is obviously doing the same thing, calcuating by row.

I have also tried some SUMX versions but they seem to total every request.

Total Hours = CALCULATE(SUMX(boardingdata,DATEDIFF([StartDate],NOW(),Hour)

Hope this is enough information, thanks for any assistance.

Re: Total & Average Hours

amitchandak — Wed, 18 Aug 2021 06:56:45 GMT

ringovski1 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Try like

AVG Duration HRS =calculate(DIVIDE([Duration HR], count(boardingdata[PhaseTxt])), values(boardingdata[PhaseTxt]))

Re: Total & Average Hours

ringovski1 — Wed, 18 Aug 2021 07:09:33 GMT

I have found that the demintor in division is the problem. as detailed above the sum/count is wrong. So I wrote Count = COUNTROWS(VALUES(boardingdata[PhaseTxt])

This correctly returns the count of phases when used in a Card as the card sums each row. But when I try to use it in the matrix table it's row by row. So any ideas on how to get the countrows as a total?

Thanks

Re: Total & Average Hours

ringovski1 — Thu, 19 Aug 2021 02:20:23 GMT

Been trying to insert samples and they keep getting delete, IDK. You can't seem to attached CSV or XLSX.

I use MIN in the start date measure to get single row for each phase.

RequestNum,PhaseTxt,StartDt
118352,Pending Account,14/07/2021 11:13
118352,Pending Account,14/07/2021 11:13
118352,Pending Account,14/07/2021 11:13
118352,Pending Account,14/07/2021 11:13
118352,Pending Account,14/07/2021 11:13
118352,Pending Action,5/08/2021 9:10
118352,Pending Action,5/08/2021 9:10
118352,Pending Action,5/08/2021 9:10
118352,Pending Action,5/08/2021 9:10
118352,Commencement,10/08/2021 9:56
118352,Commencement,10/08/2021 9:56
118352,Commencement,10/08/2021 9:56
118352,Commencement,10/08/2021 9:56
112750,Pending Documentation,9/04/2021 11:57
112750,Pending Documentation,9/04/2021 11:57
112750,Pending Documentation,9/04/2021 11:57
112750,Pending Documentation,9/04/2021 11:57
112750,Pending Documentation,9/04/2021 11:57
112749,Pending Documentation,9/04/2021 11:57
112749,Pending Documentation,9/04/2021 11:57
112749,Pending Documentation,9/04/2021 11:57

Re: Total & Average Hours

ringovski1 — Thu, 19 Aug 2021 04:54:07 GMT

As the totals in the matrix weren't correct I created a summarizecolumns(table) and placed the Averages in cards which display the correct numbers and update correctly depending on the request num selected. But unfortunately still don't work in the matrix visual(summarize table).

Earliest TaskStartedDt = MINX(
KEEPFILTER(BoardingData[StartedDt])),
CALCULATE(MIN(BoardingData[Startdt])))

Duration HR = DATEDIFF([Earliest TaskStartedDt],NOW(),HOUR())
Duration WK = DATEDIFF([Earliest TaskStartedDt],NOW(),Week())

New Hrs = SUMMARIZECOLUMNS(BoardingData[RequestNum],BoardingData[PhaseTxt],
                                                        "StartDate",MIN(BoardingData[StartedDt],
                                                        "Hrs",[Duration HR],
                                                       "Weeks", [Duration Wk])

Count = COUNTROWS('New Hrs',[PhaseTxt])
Sum Hrs = SUM('New Hrs'[Hrs])
Sum Wk = SUM('New Hrs'[Weeks])
AVG Duration WK = DIVIDE([Sum Wk],[Count])
AVG Duration Hrs = DIVIDE([Sum Hrs],[Count])