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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ringovski1
Regular Visitor

Total & Average Hours

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)

 

phases.JPG

 

 

 

 

 

 

 

 

 

 

 

Hope this is enough information, thanks for any assistance.

4 REPLIES 4
ringovski1
Regular Visitor

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])

ringovski1
Regular Visitor

 

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

ringovski1
Regular Visitor

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

amitchandak
Super User
Super User

@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]))

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors