cancel
Showing results for
Did you mean:
Super User

## How to sum columns (hours) in a matrix?

I'm still pretty new to PBI. I have Free PBI Desktop v2.100.1182 (64 bit free version Dec 2021) on Windows 10.

I have made a matrix which sums many types of paid hours for an employee. I need to make a calculated column to calculate "PossibleHours" on which I will base overtime. But not all payroll types (ptypes) are added up for overtime. Here's an image of what I have so far.

I need to add another column to the far right called "Possible hours" which sums up hours for each person payroll types 5007, 9, 5018, 1 and 6. So the formula should sum up these hours as we may add more payroll types in the future. I already have a date filter which looks at hours for one payroll week.

Is this even possible? I have done some internet searches but no one else's data looks like mine, and I have not found any hints on how to do this.

Once I get "Possible Hours" working I will try to calculate Overtime on my own.

A link to a zip file with a PBIX file and supporting Excel file is here: https://gilsongraphics-my.sharepoint.com/:u:/p/croberts/EcE6N9xyQfFItDLuzIDqMtoBxjAHXhjRkI_lBXkZk4Xe...

PossHrsMeas = CALCULATE(SUM(EmpTime[Total Hours]),FILTER(EmpTime,(EmpTime[ptype]=1) && (EmpTime[ptype]=5007)))

It gets no errors but I am unable to drag it to a matrix. I don't think that's how a matrix works. The columns are already calculated to be the field Emptime[ptype] so I cannot drag anything else to the matrix.

Thank you!

EDIT: I come from a programming and database background. From my point of view databases should not be "flattend" to be a single table. But in one PBI tutorial, they did just that. They took multiple tables in the Transform stage of PBI, and made it so the one main table appeared flattened. I.e. Sales category IDs were changed to the actual sales category name, employee IDs were changed to the actual employee name, etc.

13 REPLIES 13
Super User

Hi @croberts21
Here is the file with the error fixed https://www.dropbox.com/t/DSsJS7MvaCrVU1Wu

PossHrsMeas1 =
IF (
HASONEVALUE ( PayrollTypes[Payrollcode] ),
SUM ( EmpTime[Total Hours] ),
CALCULATE (
SUM ( EmpTime[Total Hours] ),
FILTER ( PayrollTypes, PayrollTypes[Payrollcode] IN { 1, 5007, 5018, 6 } )
)
)

Super User

Thank you, but PBI won't let me drag PossHrsMeas1 to be on the far right column in the matrix. I'd like to show PossHrsMeas1 on the matrix as the far right column. Then based on PossHrsMeas1 I will determine if the person gets overtime for that week.

Super User

It is only one measure in visual. I don't get you

Super User

Sorry I cannot your file right as I'm not on my PC. would you please confirm that the numbers shown in the matrix are the results of one measure and that on column headrers we can see the vslues of one of the source data columns. If this is the case then please share the code of the measure and the name of the column.

Super User

The column DeptIdDesc is simply a calculated column which concatenates the dept id and its description. The employee names is also a calculated column which concatenates the first name, last name, and employee id together. The PtypeNameCode is also a calculated column which concats the payroll type short name and number.

The Matrix itself is very simple with no other calculated columns.

In the Values area of the matrix is "Total Hours" which is just a renamed field from the database, not a measure or calculated column.

The matrix setup is here:

Super User

you may try the following. Please make sure the data type of the TypeNameCode column is text (string) type

you need then to show the row totals were the expected result shall show there. I guess it is possible to rename it

PossHrsMeas =
IF (
HASONEVALUE ( EmpTime[DeptldDesc] ) || HASONEVALUE ( EmpTime[Name] )
|| HASONEVALUE ( EmpTime[startdate] ),
SUM ( EmpTime[Total Hours] ),
CALCULATE (
SUM ( EmpTime[Total Hours] ),
FILTER ( EmpTime, EmpTime[ptype] IN { "1", "5007", "518", "6" } )
)
)

Super User

I have a field called PayrollTypes[PtypeNameCode]. I think that is what you meant for TypeNameCode, which is a string.

Here are the relevant tables and fields I'm working with.

When I try to drag the PossHoursMeas field you have above, to the matrix, I get an error "Can't display the visual. See details."

As far as showing row totals, that is already on the viz at the bottom of each column, right? See screen shot in my first post.

Super User

Try this

PossHrsMeas =
IF (
HASONEVALUE ( EmpTime[DeptldDesc] ) || HASONEVALUE ( EmpTime[Name] )
|| HASONEVALUE ( EmpTime[startdate] ),
SUM ( EmpTime[Total Hours] ),
CALCULATE (
SUM ( EmpTime[Total Hours] ),
FILTER ( PayrollTypes, PayrollTypes[ptype] IN { "1", "5007", "518", "6" } )
)
)
Super User

Please don't take this the wrong way, I greatly appreciate your help. But this might work better if you are able to take a look at the PBIX file, even if it takes a few days. The link is above.

Also, the DeptIdDesc comes from the Department table so would be written as Department[DeptIdDesc]. 🙂

And by table view do you mean the 3 vertical icons on the far left of PBI? Because when I go there it doesn't show me any calculated columns or measures. It only shows me normal fields from my data source.

Correction, "OT Col" is a calculated column and the only calculated column or measure that is shown in table view.

Super User

Ok I checked the file here it is with solution https://www.dropbox.com/t/OOMTUOTSyQJqHlcL

PossHrsMeas1 =
IF (
HASONEVALUE ( Department[DeptIdDesc] ) || HASONEVALUE ( EmpTime[Name] )
|| HASONEVALUE ( EmpTime[startdate] ),
SUM ( EmpTime[Total Hours] ),
CALCULATE (
SUM ( EmpTime[Total Hours] ),
FILTER ( PayrollTypes, PayrollTypes[Payrollcode] IN { 1, 5007, 518, 6 } )
)
)
Super User

Ok I see the file and I opened it. For the matrix, in the Rows area, I had to have the fields in this order: DeptIdDesc, Name, startdate, and that fixes that part.

But in the Total column it's still adding up PMLA (5031) for Amy Green and it shouldn't. The Total should sum columns FUN, HOL, JURY, REG, and VAC. And we might add more of those fields that will add to overtime in the future.

I don't see the measure PossHrsMeas1 on the matrix either. But that's what would sum up hours that are available for overtime. PMLA does not count towards overtime. But FUN, HOL, JURY, REG, and VAC do count towards overtime.

Super User

Ok. I'll look into it and get back to you

Super User

I mean column totals. Can you please open the tables view and make a screenshot of tge calues of that column. Aslo please send the details of the error

Announcements