Forum Discussion

buinia's avatar
buinia
Frequent Visitor
9 years ago

Column Total not taking into account Filtered Field

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 b...
Need Help
Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

buinia

 

What is the dax to [Staff Hours]'

 

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

buinia's avatar
buinia
Frequent Visitor
9 years ago

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's avatar
    v-micsh-msft
    Microsoft Employee
    9 years ago

    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's avatar
      buinia
      Frequent Visitor
      9 years ago

      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's avatar
        v-micsh-msft
        Microsoft Employee
        9 years ago

        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