Forum Discussion
Total column on Matrix BI report not summarizing full row
Hi All,
I am very new to Fabric Power BI (not as freindly as SSRS)
I am trying to create a simple matrix report from a Semantic model which was created from a Lakehouse view.
In the view I have agents in my row
I have dates on my column which I populate with hours spent in the values. Hour spent is formatted as 2:30 for 2.5 hours its a time field.
how do I get the matrix to sumarize my row of time? I created measures which seemd to be fine in the model, but failes to display in the matrix...
| Agent | 2/3/25 | 2/4/25 | 2/5/25 | this s/b total hours how do I create? | |
| jondoe | 01:00 | 06:00 | 09:30 | 16:30 | |
| samdoe | 02:30 | 08:00 | 09:30 | 20:00 |
TIA,
Joe
Hi Anonymous,
Thank you for reaching out to Microsoft Fabric Community.
It looks like the Matrix Total column is not summing up time values as expected. Since power bi treats time fields as datetime rather than numerical values, it does not aggregate them correctly in the matrix.
Since the Hour spent column is stored in time format (HH:MM), power bi needs a measure to sum it properly.
Create a measure that converts time into minutes, sums it, and then converts it back to HH:MM format. Below is the measure:TotalHours =
VAR TotalMinutes = SUMX(
'AgentHours',
HOUR('AgentHours'[Hours Spent]) * 60 + MINUTE('AgentHours'[Hours Spent])
)
RETURN
FORMAT( INT(TotalMinutes / 60), "00" ) & ":" & FORMAT( MOD(TotalMinutes, 60), "00" )
Add this Measure to the Values field in the Matrix visual. The Total Column will now correctly show summed up time in HH:MM format.Note: By default Date feild will be categorised as Date Hierarchy, change it to Date.
Please find the below attached PBIX file for your reference.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
3 Replies
- GilbertQ
Super User
Hi Anonymous
What I would recommend doing is converting your time into minutes and then using a measure to display the values where gets converted back to hours and minutes. The reason for using the measure is you can then get the totals. Here is an example
Convert from Seconds to Minutes with a DAX Measure - FourMoo | Microsoft Fabric | Power BI
v-achippaCommunity Support
Hi Anonymous,
Thank you for reaching out to Microsoft Fabric Community.
It looks like the Matrix Total column is not summing up time values as expected. Since power bi treats time fields as datetime rather than numerical values, it does not aggregate them correctly in the matrix.
Since the Hour spent column is stored in time format (HH:MM), power bi needs a measure to sum it properly.
Create a measure that converts time into minutes, sums it, and then converts it back to HH:MM format. Below is the measure:TotalHours =
VAR TotalMinutes = SUMX(
'AgentHours',
HOUR('AgentHours'[Hours Spent]) * 60 + MINUTE('AgentHours'[Hours Spent])
)
RETURN
FORMAT( INT(TotalMinutes / 60), "00" ) & ":" & FORMAT( MOD(TotalMinutes, 60), "00" )
Add this Measure to the Values field in the Matrix visual. The Total Column will now correctly show summed up time in HH:MM format.Note: By default Date feild will be categorised as Date Hierarchy, change it to Date.
Please find the below attached PBIX file for your reference.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
