Column Total not taking into account Filtered Field

[buinia]

Hi there,

I'm having an issue with the column total in Power BI not showing correctly.

I have a table of all staff and their department/SBU within the company, in some cases, staff can move between different department/SBU as internal transfer. Their timesheet will be under 2 different SBUs during the year of transfer.

I can see the time has been split out correctly under the correct SBU the staff belongs to at the time, however the column total shows the same total number of hours the staff entered but did not split it out between the 2 SBUs. Would any of you have a solution to this issue?

I did try using My Total = CALCULATE(SUM('[Staff Hours]'),ALLEXCEPT('Master Staff','Master Staff'[SBU]) but it didn't work.

Many thanks in advance!

Vivian

[Vvelarde]

@buinia

What is the dax to [Staff Hours]'

You have 1 table or 2 tables. what is his relationship?

[buinia]

@Vvelarde

There is no dax to [Staff Hours], it is just timesheet (daily hours) which have been loaded by staff members.

The tables/fields involved in this:

1. Staff Department Master - [Staff Name] - [SBU] - [Month]

2. Staff Master - unique [Staff Name]

3. SBU Master - unique [SBU]

4. Staff Timesheet - [Staff Name] - [Staff Hours] - [Month]

All the master tables are related Many to One - Cross filter Both direction, and they are related to the Staff Timesheet by Many to One - Single cross filter.

Thanks

[v-micsh-msft]

Hi buinia,

I suspect this should be related with the data table.

Under the table where [Staff Hours] stated in, we need a column to mark the SBU, this should have Matrix Visual to the show the correct total number.

By the way, would you please share a screenshot of the relationship View in Power BI desktop regarding your tables? Only following the data you shared is a little for me to figure out the relationship between those tables.

Regards

[buinia]

Thanks @v-micsh-msft,

I'm not too sure if you can see the relationships from my file since I have a lot...

The general idea is to work out client profitability by taking Revenue - Staff Costs

Revenue is broken further to SBUs based on product type

Staff Costs = Hourly Rate * Hours input through Timesheet

I would really appreciate if you could help me find out a solution to this. Thanks

[v-micsh-msft]

Hi buinia,

Apologize for the late response.

I would argee with that it would be a little diffcult for me to figure the relationship out with the image you shared.

I saw that the relationship on SBU Master are both one-to-many, which would bring some query issues.

What I would suggest would be to add a SBU column under the Staff Timesheet table, which would bring the correct total in the visuals.

For the other part, I would prefer to rebuild the data model, and the relationship, to make the data work better in Power BI.

Just from what I have seen from the image, I would suggest the followings:

1. Create Table for different category, and each table should contain at least one distinct column;

2. build the relationship, follow the guide here.

For example, we could build a special date table to manage the date related things, first column containing the continuous date, and build the other columns (such as the month related information) based on the date column, and the other tables should have one column in date format and we only need to build many-to-one relationship to the date table here.

Regards