Forum Discussion
Column Total not taking into account Filtered Field
Microsoft EmployeeHi 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
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