Custom Total/ Subtotal seperate calucation

[azwief]

Here’s the problem:
I generated a pivot table from a powerpivot dataset. The table consists of a work order, this work order has many transactions related to it with actual time and parts produced for that transaction.

Unfortunatly, at the total/ subtotal level, the table is summing all the transaction’s time per piece which results in an incorrect number,
I want to calculate the total time per piece at the work order level which would be “Sum Actual Time Per Transaction”/”Sum Actual Pieces Per Transaction”

Any help to figure this out would be most appreciated. I’ve about scratched a bald spot into my head.


<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"></o:lock></vath></v:stroke></v:shapetype><v:shape style="width: 564.75pt; height: 287.25pt; visibility: visible; mso-wrap-style: square;" id="Picture_x0020_1" type="#_x0000_t75" o:spid="_x0000_i1025"> <v:imagedata o:title="" src="file:///C:\Users\azwiefel\AppData\Local\Temp\msohtmlclip1\01\clip_image001.jpg"></v:imagedata></v:shape><o></o>
<o></o>

 

[powerpivotpro]

You probably need something like IF(HASONEVALUE(), X, Y). This post is likely more complex than you need but may give you the idea:

HASONEVALUE vs. ISFILTERED vs. HASONEFILTER « PowerPivotPro