Analytics Software

I have a cross tab

left axis
employee number
employee name

Top axis
Item Name (Basic Hours, Basic Pay)

data points
Value (Number)

I need to create a calculation based on the total hrs worked item and basic pay item i.e. I have tried:

SUM(CASE WHEN "XXPAY Key Payments And Deductions".Item Name = 'Basic Pay' THEN TO_NUMBER("XXPAY Key Payments And Deductions".Value) ELSE 0 END)

With the hope that I could get the item value for basic pay onto the left axis and do the same for basic hours then just do another calculation based on these two i.e. basic pay/basic hours = rate

The aggregation seems to be an issue and I get 2 lines appearing on the report i.e. because we are interogating a detail item that is included onb the top axis. Is their any way of doing this and getting around the aggregation issue, basically I need to use the datapoint values in calculations dependant on the item name.

Thanks

Keith