Calculation based upon subtotal in group

[CLEARIFY]

This should be very simple, but I am obviously missing something here - PowerPivot 2013.

In this example I am trying to get revenue per employee (highlighted in yellow)

In order to do that I need the numerator to appear in every row (red arrow in subtest)

I thought this would do the trick, but ...=CALCULATE(sum('JOB COST DETAILS'[Job Line Income Amount]),ALLEXCEPT(Employee,Employee[Employee Name]))

This example only has one job name, that would normally appear in a list of many jobs.

 

[XLBob]

This should be very simple, but I am obviously missing something here - PowerPivot 2013.

In this example I am trying to get revenue per employee (highlighted in yellow)

In order to do that I need the numerator to appear in every row (red arrow in subtest)

I thought this would do the trick, but ...=CALCULATE(sum('JOB COST DETAILS'[Job Line Income Amount]),ALLEXCEPT(Employee,Employee[Employee Name]))

This example only has one job name, that would normally appear in a list of many jobs.

are you trying to populate the column with red arrow with the same number (163,205). try CALCULATE(sum('JOB COST DETAILS'[Job Line Income Amount]),ALLEXCEPT(Employee,Employee[Job Name]))

 

[CLEARIFY]

are you trying to populate the column with red arrow with the same number (163,205). try CALCULATE(sum('JOB COST DETAILS'[Job Line Income Amount]),ALLEXCEPT(Employee,Employee[Job Name]))

Job name is not part of the employee table, but if I used =CALCULATE(sum('JOB COST DETAILS'[Job Line Income Amount]),ALLEXCEPT(Job,Job[Job Name])) I still don't get the same result.

 

[XLBob]

Can you upload a sample workbook if possible?

 

[CLEARIFY]

Can you upload a sample workbook if possible?

This is 2013 PowerPivot

http://www.clearify.com/downloads/Job_Cost_test.xlsx

Thank you for helping out. Hard to believe that it is that hard to get a sum of field by group to perform calculations.

 

[XLBob]

in your formula you have used ALLEXCEPT(Employee,Employee[Employee Name])), which removes all filter coming from your pivot table. Is this what you really want? try ALL(Employee,Employee[Employee Name]) instead.

you have a couple slicers in your report I would assume you want to keep the filer context coming from those.