Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a slicer of months:
I have a table with date and hours worked (table is 23 columns, 18,000+ lines but to simplify):
Date | Hours Worked |
12/1/2019 | 122.75 |
I want to add up the hours worked and divide by total hours for the month(s) depending on what months are checked in the slicer.
I'm not sure where the best place to keep the total hours for the month are. the date dimension? just write them into a formula?
Month | Total Hours |
December | 177.1 |
January | 177.1 |
February | 165.7 |
Relationships:
Appreciate any advice!
Hi, @kressb
Could you please tell me whether your problem has been solved?
If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello,
@amitchandak @Pragati11 @v-easonf-msft
Trying to provide more data but I'm not sure what is needed.
Hours Table looks like this:
Date | Name | Department | Cost Center | Pay Code | Hours |
7/1/2019 | Susie Smith | PowerBI | 1234 | WORK | 80 |
7/2/2019 | Tim Green | PowerBI | 1234 | WORK | 78 |
9/10/2019 | Jim John | PowerBI | 1234 | WORK | 77 |
Date Dimension looks like this:
Date | Month | Year | Total Work Hours for Month | FY Number |
7/1/2019 | July | 2019 | 177.1 | 1 |
7/2/2019 | July | 2019 | 177.1 | 1 |
9/10/2019 | September | 2019 | 165.2 | 3 |
I want to take HoursTable.Hours and Divide by Total Work Hours in July to get a number, ex:
July: (80 + 78) / 177.1 = 0.89
September: 77/165.2 = 0.47
But since my date dimension lists every date, I don't think the "Total Work Hours" is set up correctly.
Not sure if I should create a whole new table, ex:
Month Year | Total Work Hours |
July 2019 | 177.1 |
September 2019 | 165.2 |
and connect it somehow to the other data sets?
I also want to be able to project based on FY Number, ex:
July: (80+78) / 1 * 12 = 1896
September: 77/3*12=308
Hi @kressb ,
Can you please add more detail for your query? The information seems to be less to answer your question.
Thanks,
Pragati
Hi , @kressb
You can do it any way you like.
In my case, I would create a calculated column in the table with date and hours worked.
You may check if this sample file help.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@kressb ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share a bigger sample data and sample output in table format?
Appreciate your Kudos.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |