Revenue per Employee based on hours made at customers

[Graham_00]

Hi,

First of all, I've searched the forum, but I could not find anything related to my question. If I'm mistaken, please accept my apologies.

My need for help is as follows:

We have co-workers that go to several  customer during the year in which they spend several hours.
On the other side, we wil bill our customers during the year several times. There is not always a direct match between de dates of the hours spend and billingdate and there is no specific/exact relationship between the hours and billing (some of the hours are part of a contract - some are part of actual hours spent.

What I would like to calculate is the revenue per employee per customer per period (=month). So let's say, Customer X had a revenue in period of USD 1200 and the total hours spend during this period by employees at this customers is 12, then my average hourly rate would be USD 100.
Employee Z, who had spent that period at that customer 2 hours, has a revenue of 2 x 100 = USD 200.

I have the followin tables:

And the following Active relation ships:
Calender(Date) -> RevenueTable (Date) - 1 to many

HoursTable (Customers) -> RevenueTable(Date) - 1 to many

CustomerTable(No) -> RevenueTable(CustomerNo) - 1 to many

An inactive relationship is:
Calender(date) - HoursTable(Date)  - 1 to many.  ((when I activate this one, an error messag pops up. Because I have a Dutch language package of Powerbi I cannot easily translate this one, but it sayas something like "There cannot be a direct active relationship, because that will cause a "double something" between table CustomerTable and RevenueTable...."

In the end I hope to be able to see something like I have in Excel ( I showed the tables as well).
The calculated results is for the total period, but I would like to see the results also for any giving period (resulting in different  hourly rates).

Thank your very much for any help!

Regards,

G.

[tamerj1 · Accepted solution]

Hi @Graham_00 
Here is the sample file with the solution https://www.dropbox.com/t/awqpQqvLchAxqInV
It is working, however I don't believe this a proper way of making such calculations. You have to have a transaction number that links the sales table with the Man-Hour table otherwise there is no way to determine which employees belong to which invoice. 
I understand your method to work around that which is by calculating a fixed hourly rate per customer. I followed the same, but again, which employees belong to this customer? This is something that cannot be determined without a transaction number that exists in both tables.
The data model looks like this

The measures are 

M-TotalSales = SUM ( RevenueTable[Revenue] )

M-Hours = 
CALCULATE ( 
    SUM ( HoursTable[Uur] ),
    USERELATIONSHIP ( CustomerTable[No], HoursTable[Customers] )
)

Hourly Rate = 
CALCULATE ( 
    DIVIDE ( KMeasures[M-TotalSales], KMeasures[M-Hours] ),
    REMOVEFILTERS ( Calender )
)

Total Revenue = [M-Hours] * [Hourly Rate]

[tamerj1]

Hi @Graham_00 

can you please provide sample file with the same data presented in your description?

[Graham_00]

@tamerj1 

I added a link, perhaps that helps Link to file 

[Graham_00]

I would like to, but I do not see how to add a excel file (database) or the .pbix file.

[tamerj1 · Accepted solution]

Hi @Graham_00 
Here is the sample file with the solution https://www.dropbox.com/t/awqpQqvLchAxqInV
It is working, however I don't believe this a proper way of making such calculations. You have to have a transaction number that links the sales table with the Man-Hour table otherwise there is no way to determine which employees belong to which invoice. 
I understand your method to work around that which is by calculating a fixed hourly rate per customer. I followed the same, but again, which employees belong to this customer? This is something that cannot be determined without a transaction number that exists in both tables.
The data model looks like this

The measures are 

M-TotalSales = SUM ( RevenueTable[Revenue] )

M-Hours = 
CALCULATE ( 
    SUM ( HoursTable[Uur] ),
    USERELATIONSHIP ( CustomerTable[No], HoursTable[Customers] )
)

Hourly Rate = 
CALCULATE ( 
    DIVIDE ( KMeasures[M-TotalSales], KMeasures[M-Hours] ),
    REMOVEFILTERS ( Calender )
)

Total Revenue = [M-Hours] * [Hourly Rate]

[Graham_00]

Hi @tamerj1 ,

Once again thank you for your solution above. 
However, in my supplied .pbix report, the relationship between Calender and HoursTable is 1 to 1.
However, I've the hourstable has more data with some rows with the same date, the outcome is not correct.
The initial post is ofcourse closed (with for that particular) case the right solution. Should I create a new post with this new question?

[tamerj1]

@Graham_00 

This is not sn optimum solution anyway. However, the cardinality of the relationship betwmeen Calendar and HoursTable is not an issue as long as Calendar filters HoursTable. Actually it should be easier as now the inactive relationship between HoursTable and Customer Table can be active and no need to use USERELATIONSHIP  

[Graham_00]

I changed the relationship from 1:1  to 1 to many and activated the relation between Customer table and Hourstable.
The hours per employee per customers are okay, so is the calculation of the hourly rate. 
However, the revenue per employee per customer does not go right.

So for example Employee Jan worked for customer ABC 10 hours - that customer has a hourly rate of 61,47 (which is correct)
10 x 61,47 = 614,70
However, the revenue is in the report for Jan/ABC = 225

Did I do something wrong?

[tamerj1 · Accepted solution]

@Graham_00 
Here is the updated file https://www.dropbox.com/t/eFuZuKsLgGvqhrrB

In order to fix the rate of each customer, I created it as a calculated column in order to take it completely out of the filter context. > New Column >

Hourly Rate = 
VAR Hrs =
    SUMX ( RELATEDTABLE ( HoursTable ), HoursTable[Uur] )
VAR Revenue =
    SUMX ( RELATEDTABLE ( RevenueTable ), RevenueTable[Revenue] )
RETURN 
    DIVIDE ( Revenue, Hrs )

Then the measures are

M-Hours = 
SUM ( HoursTable[Uur] )

M-Hours = 
SUM ( HoursTable[Uur] )

Total Revenue = 
SUMX ( 
    CustomerTable,
    CustomerTable[Hourly Rate] * [M-Hours]
)

Again, this is not so accurate but it gives a good estimate. Thank you and have a good day!

[Graham_00]

@tamerj1 

You've made my day!  Thank you very much!

[tamerj1]

@Graham_00 

You are right. But the case since beginning. I explained earlier that due to the lack of a transaction key that can link between the tables and there is no way to verify which employee spint which hours serving which customer. And since the dates in the employee report do match the customer report dates revenue values will not match. However, please update the sample file with more data perhsps to better reflect yhe actual case and let me see if I can do something 

[Graham_00]

I truly understand.
In my simple mind, there whil be indeed a mismatch between dates hours made vs billed.
That's something I can live with 😉
Here is a link to the new .pbix report report .

Your help is really appreciated, however if it takes to much time or seems impossible, please do not spent to much of your time.

[tamerj1]

No worries. But I won't have the chance look into it untill tomorrow morning. Hope you are ok with that. 

[Graham_00]

Take all the time you need! I'm struggeling with this issue for a year now and can wait as long as it takes you 😉 
I'm thankfull that you will take the time to look into it!

[Graham_00]

Hi @tamerj1 ,

You are amazing and thank you very, very much!

I'm aware of no direct link between the two tables. However that is due to two different systems, with no link.
One systems to bill (fixed price contracts) and the other system in which employees can write their hours.

No ideal situation, but with this report I will be able to monitor the revenue, not only by employee, but also by hour. 

This is really helpfull and you could not make me happier!

Once again thankyou!