Creating a summarized table from columns from 3 original tables

[Anonymous]

Hi all,

I'm trying to create a new table that summarizes some data on a daily basis.

The original data looks as follows:

Dim_date file: includes the dates for 2017 and 2018. Related to both tables below.

Hours 1: Hours per employee per day in database 1

Hours 2: Hours per employee per day in database 2

Hours 1 and 2 are related through the Dim_date file.

Both hour databases have a set-up like the table below.

Employee name  Date                 Hours
Employee A          1-1-2018          8
Employee B          1-1-2018          6
Employee C          1-1-2018          7
Employee D          1-1-2018          9
Employee A          2-1-2018          8
Employee B          2-1-2018          5

I would like to create the following table below, but I'm not sure if PowerBI support this functionality. Basically create a new table with the date and the total sum of hours per date for both hour tables. Like the following: 

Date           Total hours1 Total hours2
1-1-2018    30                   16
2-1-2018    13                   13
3-1-2018    20                   13
4-1-2018    13                   12
5-1-2018    15                   12
6-1-2018    12                   16

Eventually I want to be able to do some calculations of the differences between both tables. 

I've been trying to do this for some time now, but can't seem to figure it out. Any help would be appreciated!

[Zubair_Muhammad · Accepted solution]

HI @Anonymous

Try this solution

Go to Modelling Tab >>> NEW TABLE and use this formula

New Table =
SUMMARIZE (
    Dim_Date,
    Dim_Date[Date],
    "Total Hours 1", CALCULATE ( SUM ( Database1[Hours] ) ),
    "Total Hours 2", SUM ( Database2[Hours] )
)

[Zubair_Muhammad · Accepted solution]

HI @Anonymous

Try this solution

Go to Modelling Tab >>> NEW TABLE and use this formula

New Table =
SUMMARIZE (
    Dim_Date,
    Dim_Date[Date],
    "Total Hours 1", CALCULATE ( SUM ( Database1[Hours] ) ),
    "Total Hours 2", SUM ( Database2[Hours] )
)

[Anonymous]

That indeed creates the right columns, and the dates are correct. However it does not include any values for the hour columns.

I have found a work around, however not surehow sustainable this is.

I have copied the original hour tables, and grouped them by date. Then merged these two tables in to a new table by cross filtering the dates. This does create 3 extra tables though, so not ideal.

[Zubair_Muhammad]

@Anonymous

---

@Anonymous wrote:

That indeed creates the right columns, and the dates are correct. However it does not include any values for the hour columns.

---

It should give you Total Hours 1 and Total Hours 2 columns as you desired?

Do you need something else?

Could you illustrate it please

[Anonymous]

It gives the column headers, however there are no values in the cells.

[Zubair_Muhammad · Accepted solution]

@Anonymous

It works with me when I use the sample data you have provided

see the attached file here

[Anonymous]

I must have done something wrong because now it works!! Thanks so much! 🙂