Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
It works with me when I use the sample data you have provided
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] ) )
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.
@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
It gives the column headers, however there are no values in the cells.
@Anonymous
It works with me when I use the sample data you have provided
I must have done something wrong because now it works!! Thanks so much! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |