HELP! I'm stuck! Please point me in the right direction...

[CoreyP]

Hello MS Power BI Desktop Community!

After weeks of trial and error, I've come to you for help pointing me in the right direction. It's probably important to note that I am a PBI n00b, however, I feel I've learned a ton since I began this project. 

Let me start off by describing what I'm tasked with and how far I've made it. 

My mission: To create a Dashboard to allow insights into performance in a department, most imprtantly: productivity, 

So, within this department, works an always-changing staff of employees. These employees log their time per activity. There's about 30 activities, but for our purposes there's 9, I have a bunch grouped under Other. So my activities look like this: Data Entry, Inspections, Loose Pick, Mail Sorting, Putaway, Redirects, Repack, Repack Entry, and Other. 

I transform the RAW data into a table like this: Date | Lastname, Firstname | Activity | Hours

Then I transform a RAW production data set into a table like this: Date | Lastname, Firstname | Activity | Units

Now, these are obviously my 2 data tables. I have dimension tables as well. Date, among other things. 

I want to be able to calculate productivity as total units divided by total hours. I want to be able to determine this at the employee level, as well as by the Activity level. For instance, Data Entry productivity for whatever date range would equal the sum of all data entry units divided by the sum of labor hours. That's easy. 

However, because employee's don't always record their times correctly, it's possible, on any given day, to have Joe Schmo recording 2 hours of data entry labor and the production report doesn't have any. The reverse is true, someone could have Data Entry production units, but did not record any data entry hours. Also, employee schedules are changing, so the same employees don't always work the same shift. 

So... how in the world can I get to this productivity figure with the data I have now. Am i going to need to flatten/merge, or is it possible to use existing functionality within the PBI Desktop program?

PLEASE help me out! I will be eternally grateful!

[v-jianhe-msft]

Hi,

For the employee level, you could generate a third table, generating relationships with original two tables Units and Hours.

Then using expression:

Measure2 = sum(Units[Units])/sum(Hours[Hours])

Similarly, it can be done on Activity level.

By doing this, you need to add the “name” column from the third table generated.

Best Regards,

Henry

[CoreyP]

Thank you so much for the reply! As I was laying in bed last night, contemplating possible solutions, I actually thought that I'd been going about it all wrong. I had been trying to use the same data, the same table, shared by all the dimension tables, to get every possible figure/measure/calculation I wanted. I realize now, you are so correct, I just need to do multiple queries/tables, just transformed differently depending on what visualization I'll be using it for. 

I'm going to test this out, and I'll get back to you with the results!

[v-jianhe-msft]

Hi,

Thank you for your kind reply. May I know how is the issue going currently? 

Best Regards,

Henry

[CoreyP]

Well, I was waiting on my computer upgrade so I now have the hardware to actually perform what Im doing.

So I have a year's worth of data. Each day SHOULD have 5 activities.. Data Entry, Inspections, Loose Pick, Putaway, Redirects, Repack, Repack Entry.

I want to merge my queries so that it's one single table with Date | Activity | Units | Hours | ([Units]/[Hours])

Here's what my two queries look like now..

[v-jianhe-msft]

Hi,

I think you need to merge the two result set in your original dataset. For example, if you are using Excel, just paste the column into a new table. 

But the two result sets in your screenshot are not "tables", because they do not have a primary key. Which is not possible to merge them the power bi. Because merge applies SQL join function to match the columns. Which gives me a result like: 

Best Regards,

Henry