cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Tickets Per Hour Calc involving 2 different tables

The Scenario is I have 2 tables. 

Solves 

    Date

    Name (first last initial)

    Tickets Solved

 

Hours Worked

     Activity Date

     Actual Hours

     Name (Long Name)

 

The Hours Worked table has other fields but they are not used for this.  The Name i have related through a bridge table that is working very well.  The Dates are also bridged.  

 

I have set a new measure TPH S/H = SUM(Solves[# Tickets Solved])/SUM('Hours Worked'[Actual Hours])

Front.JPG

 As you can see I have a repeating number that is just not correct. 

 

Can someone show me where my error is please.

 

Thank you in advance

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: Tickets Per Hour Calc involving 2 different tables

I actually believe i figured it out

 

TPH 3v2 = DIVIDE(CALCULATE(SUM(Solves[# Tickets Solved])),CALCULATE(SUM('Hours Worked'[Actual Hours])))

 

First I created a new measure, checked the math and it seems to work out.1.JPG

 

If you have a sample up and running could you check this and make sure I am not just lying to myself lol... 

View solution in original post

9 REPLIES 9
Highlighted
Super User III
Super User III

Re: Tickets Per Hour Calc involving 2 different tables

Hi @MAS13,

 

Taking into account that you have the names and dates bridged from wich tables are you using in your table, are the ones from the bridges or from one specific table (solves or Hours Worked).

 

When do bridges you should use the dimension tables to make your visuals.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Helper I
Helper I

Re: Tickets Per Hour Calc involving 2 different tables

Thank you for the quick response.

 

I have 2 slicers on the page Name and Date. Both are from my bridge tables 

 

The matrix you are looking at contains the Date from the date table, Actual Hours from the Hours worked tbale Ticket Solves from the Solves Table and the measure i made which is also stored in the Solves Table.  

 

Could you please explain why dimension tables... Sorry limited time on the BI platform

Highlighted
Super User III
Super User III

Re: Tickets Per Hour Calc involving 2 different tables

Hi @MAS13,

 

Dimension tables = bridges tables, just a question of naming.

 

Can you share a sample of your file, or a data example of your tables and how the bridges tables are setup, so I can replicate the model.

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Helper I
Helper I

Re: Tickets Per Hour Calc involving 2 different tables

I am sorry the best I can do is provide pics due to constraints here..  

 

The bridge tables are at the top.From the manage rel screen 

 

The Name Solves field is listed as: John D

Agent Name is listed as: John Doe

I do have them side by side and works correctly within my slicer.

 

Front3.JPG 

As you can see very basic Date table so I can relate the 2 fact tables.

 

Please let me know if you need any other info, or I did not supply enough

 

Also again sorry about not being able to send you a file..  

Highlighted
Super User III
Super User III

Re: Tickets Per Hour Calc involving 2 different tables

Is the Soves (Name) equal to Agent lists (Names Solves)?

Example John Doe = John Doe or John Doe = John D

Regards
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Helper I
Helper I

Re: Tickets Per Hour Calc involving 2 different tables

Yes in my bridge table the 2 colums do line up next to each other and when i change my slicer it does change the data correctly for both fact tables in a test i did

Highlighted
Helper I
Helper I

Re: Tickets Per Hour Calc involving 2 different tables

John Doe = John Doe|John D

Highlighted
Helper I
Helper I

Re: Tickets Per Hour Calc involving 2 different tables

I actually believe i figured it out

 

TPH 3v2 = DIVIDE(CALCULATE(SUM(Solves[# Tickets Solved])),CALCULATE(SUM('Hours Worked'[Actual Hours])))

 

First I created a new measure, checked the math and it seems to work out.1.JPG

 

If you have a sample up and running could you check this and make sure I am not just lying to myself lol... 

View solution in original post

Highlighted
Super User III
Super User III

Re: Tickets Per Hour Calc involving 2 different tables

Hi @MAS13,

 

Believe it's correct. I wasn't abble to make a mockup data but seem ok.

 

Regards.

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors