Column Total not taking into account Filtered Field

Question

Hi there,&nbsp;I'm having an issue with the column total in Power BI not showing correctly.&nbsp;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.&nbsp;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?&nbsp;I did try using My Total = CALCULATE(SUM('[Staff Hours]'),ALLEXCEPT('Master Staff','Master Staff'[SBU]) but it didn't work.&nbsp;Many thanks in advance!&nbsp;Vivian

vvelarde · Answer

buinia&nbsp;What is the dax to [Staff Hours]'&nbsp;You have 1 table or 2 tables. what is his relationship?

buinia · Answer

Vvelarde&nbsp;There is no dax to [Staff Hours], it is just timesheet (daily hours) which have been loaded by staff members.&nbsp;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]&nbsp;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.&nbsp;Thanks

v-micsh-msft · Answer

Hi buinia,&nbsp;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&nbsp;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 · Answer

Thanks&nbsp;v-micsh-msft,&nbsp;I'm not too sure if you can see the relationships from my file since I have a lot...&nbsp;The general idea is to work out client profitability by taking Revenue - Staff Costs&nbsp;Revenue is broken further to SBUs based on product type&nbsp;Staff Costs = Hourly Rate * Hours input through Timesheet&nbsp;I would really appreciate if you could help me find out a solution to this. Thanks&nbsp;

v-micsh-msft · Answer

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&nbsp;&nbsp;

Forum Discussion

Column Total not taking into account Filtered Field

5 Replies

Related content

Slicer for filtering several columns

Community account support

Power BI Account Blocked

Apply data to all rows in a filtered column

Your account is blocked, weve detected suspicious activity on your account.