Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kressb
Helper V
Helper V

Dividing by a Number Based on a Slicer

I have a slicer of months:

DateDimensionSlicer3.PNG

I have a table with date and hours worked (table is 23 columns, 18,000+ lines but to simplify):

DateHours Worked
12/1/2019122.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?

MonthTotal Hours
December177.1
January177.1
February165.7

Relationships:

TableRelationships.png

Appreciate any advice!

6 REPLIES 6
v-easonf-msft
Community Support
Community Support

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:

DateNameDepartmentCost CenterPay Code Hours
7/1/2019Susie SmithPowerBI1234WORK80
7/2/2019Tim GreenPowerBI1234WORK78
9/10/2019Jim JohnPowerBI1234WORK77

 

Date Dimension looks like this:

DateMonthYearTotal Work Hours for MonthFY Number
7/1/2019July2019177.11
7/2/2019July2019177.11
9/10/2019September2019165.23

 

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 YearTotal Work Hours
July 2019177.1
September 2019165.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

 

 

Pragati11
Super User
Super User

Hi @kressb ,

 

Can you please add more detail for your query? The information seems to be less to answer your question.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

v-easonf-msft
Community Support
Community Support

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.

sample file

 

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.

amitchandak
Super User
Super User

@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.

AntrikshSharma
Community Champion
Community Champion

You can store the monthly hours in the same table as the hours worked, since the cardinality would be just 12(considering the data isn't also segregated by years) that won't increase the size of the model.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.