Help with calculations "Revenue by employee"

[Danny-Happy]

I've got a question about some calculations.

We have several project with "Revenue" and "Hours spend" by employee.

This is what we get from our data:

But what we really would like to see is:

I tried to calculate the revenue by employee by: 

Revenue per hour = divide ( revenue , hours )

Revenue per employee = Revenue per hour * Hours

Unfortunately that doesn't work.

Can you please help us calculate the revenue by employee?

Thanks 🙂 

[v-xicai · Accepted solution]

Hello @Danny-Happy ,

You can create measures such as DAX below.

Percent Hour= 
DIVIDE (CALCULATE(SUM(Table1[Hours]),FILTER(ALLSELECTED(Table1), Table1[Project] =MAX(Table1[Project])&&Table1[Employee] =MAX(Table1[Employee]))), 
CALCULATE(SUM(Table1[Hours]),FILTER(ALLSELECTED(Table1), Table1[Project] =MAX(Table1[Project]))))

Percent Revenue= 
DIVIDE (CALCULATE(SUM(Table1[Revenue]),FILTER(ALLSELECTED(Table1), Table1[Project] =MAX(Table1[Project])&&Table1[Employee] =MAX(Table1[Employee]))), 
CALCULATE(SUM(Table1[Revenue]),FILTER(ALLSELECTED(Table1), Table1[Project] =MAX(Table1[Project]))))

Best regards

Amy

Community Support Team _ Amy

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Danny-Happy]

Thank you so much, this works indeed.

But now I have some new problems with the totals.

As you can see the "Totaal" is wrong from colum % Hours, we just need the following:

Revenue Employee = Totaal revenue (17.823.115,71 so just the project totals)
Revenuevalue Employee = Totaal revenue (17.823.115,71 so just the project totals)

Is this possible?

Thanks

[FrankAT]

Hi @Danny-Happy 

with your description and sample data I get the following result. Both measures returns the same values, it's by your data:

Sum of Hours = SUM('Table'[Hours])

Sum of Revenue by Employee = SUM('Table'[Revenue by Employee])

Revenue per Employee = 
    DIVIDE([Sum of Revenue by Employee],CALCULATE([Sum of Revenue by Employee],ALL('Table')))

Revenue per Hour = 
    DIVIDE([Sum of Hours],CALCULATE([Sum of Hours],ALL('Table')))

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Danny-Happy]

Thank you for your answer.

The following measure doesn't work because in "Table" there are more Projects, so the result of the measure is all written hours:

Revenue per Hour =
DIVIDE([Sum of Hours],CALCULATE([Sum of Hours],ALL('Table')))

To show this I've made the following measure:

Revenue per Hour (basically this is all written hours no revenue) =

CALCULATE(

[hours] ,

ALL( 'Fact - post-calculation total' ) )

Solution:

I can't use the following measure because I just have the revenue per project not per employee:

Revenue per Employee =
DIVIDE([Sum of Revenue by Employee],CALCULATE([Sum of Revenue by Employee],ALL('Table')))

I need to calculate the revenue per employee first based on written hours.