Column Totals for Time Column Formatted as Text (hh:nn)

[oliverblane]

Hi there.

I am trying to build a matrix that breaks down time spent on courses in different regions. I have used a measure to display the number of seconds spent as a string in the format hh:nn:ss, which works fine.

However, I need to remove the seconds from this string to become hh:nn, which is causing issues with the way that the column total is calculated. The total is no longer always equal to the sum of the column rows, since the total is summing the time spent on courses and then converting to hh:nn instead of converting the time spent for each matrix row to hh:nn and then summing.

Whether I round down or round up the minutes, the issue will remain. For example, see below where I have rounded down the minutes, and the Total_Time (hh:nn) column values do not sum to the total (58:40 + 15:57 = 74:37): 

I understand why the total is 74:38, since it has summed the hh:nn:ss values for each row and then rounded the minutes down, instead of rounding down the hh:nn:ss values and then summing. Is there a way to change this behaviour for a matrix column total?

[v-xiaosun-msft]

Hi @oliverblane ,

It is because that when a Measure is used on rows in a table, the column total for those rows is not calculated based on a sum of the results in the rows, but instead it calculates using the same Measure formula and applies it to the total of the data selected.

You can reference the following document.

How to Make Measures Total Correctly in Power BI Tables - ArcherPoint

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler · Accepted solution]

@oliverblane First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8