cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
croberts21
Super User
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. 

croberts21_0-1650540123523.png

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...

I made this measure:

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
tamerj1
Super User
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 } )
    )
)

1.png

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.

@croberts21 

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

tamerj1
Super User
Super User

@croberts21 

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. 

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:

croberts21_0-1650543028899.png

 

Hi @croberts21 

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" } )
    )
)

 

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. 

croberts21_0-1650549331664.png

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.

@croberts21 

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" } )
    )
)

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. 

croberts21_0-1650550577545.png

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

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

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. 

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

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors