sum up the man hours spent working from 5 different tables with different dates grouped together

[ssingleton]

I am new to power bi and tried to search for a similar issues but was unsuccessful. 

I have 5 different tables of data. Each of the 5 tables has a "date" column, and to "man hours" column, and others. Table A may have entries for everyday, table B may have entries every other day, table C may have entries for every 3rd day...

When I graph them with a line graph individually and group the dates into bins for each month I get very nice graphs. However if i try to put more than one of the tables together it sums all the man hours (from the second table that i try to add) into one total and I get the same total of man hours for each month (it shows a flat line). The same thing happens to the "man hours" no mater which is the 2nd, 3rd, 4th or 5th table to be inserted. Can anyone help me with this?  Thanks

[v-yulgu-msft · Accepted solution]

Hi @ssingleton,

Two options for your reference.

Option1

Create a date dimention table that lists continual date values. Create one to many relationship from this dimention table to the other five tables. Group the dates into bins based on the date field of dimention table. And add it to chart axis.

Option2 

When I graph them with a line graph individually and group the dates into bins for each month I get very nice graphs. However if i try to put more than one of the tables together it sums all the man hours (from the second table that i try to add) into one total and I get the same total of man hours for each month (it shows a flat line). The same thing happens to the "man hours" no mater which is the 2nd, 3rd, 4th or 5th table to be inserted. Can anyone help me with this?  Thanks

Create measures to sum man hours for 2nd, 3rd, 4th and 5th table rather than directly draggig the fields into chart. Measure can be similar to:

Sum of man hours =
CALCULATE (
    SUM ( Table2[Man hours] ),
    FILTER (
        ALL ( Table2 ),
        YEAR ( Table2[Date] ) = YEAR ( MAX ( Table1[DateCompleted(bins)] ) )
            && MONTH ( Table2[Date] ) = MONTH ( MAX ( Table1[DateCompleted(bins)] ) )
    )
)

Best regards,

Yuliana Gu

[edhans]

I am not 100% sure I grasp what your model looks like, how the tables are joined, etc.

But for starters, don't drag fields into the values section of a chart. You should only drag measures as a rule. So create a measure like:

Man Hours = SUM(MyTable[Hours])

It will automatcally be as granular as your data in the Axis. (Daily, monthly, etc).

You can change the aggregations of values by clicking on the dropdown next to each value, but measures are generally the better way to go as you always have total control of how it is aggregated. Explicit Measures good, implicit measures bad. Implicit measures are what you get when you drag a field into a value part of a visual. You are letting Power BI make your decision for you.

I'm not clear what your model should even look like. Are you wanting one table with all hours in it and you are getting the data from 5 different tables that need to be combined? That is probably a job of Power Query.

Or are you trying to combine 5 different divisions/departments and they are all reporting differently (daily, every other day, etc.) I'd still be inclinded to get it into one table with Power Query and have it segregated by division/department in a normalized table, then report off it, so the source table loaded into the model might look like:

Dates     Department     Hours
1/1/2018 Accounting      10
1/1/2018 Sales                8
1/2/2018 Accounting       12
1/3/2018 Accounting       10
1/3/2018 Sales                4

But I'm taking a bit of a shot in the dark given what I know about your data and goal.