Problem with Calculated field not doing expected

[masplin]

I have a table with "commission %" and "real commission charge". If I divide real commission charge by commission % I would get back to the original price. So I added a calculated field with that formula 'real commission charge'/'commission %'. I chose sum as I want the sum of the item prices. Oddly what it seems to have to done is sum the commission charges and divided that by the sum of the commission %. Have I done something wrong in my formula?

This is the data for one user (commission % is 10). So calc it has done is (1.995+2.995)/(10+10+10+10) = 0.124. I wanted (1.995/10)+(2.995/10)+(0/10)+(0/10)=0.499

<table border="0" cellpadding="0" cellspacing="0" width="662"><colgroup><col style="mso-width-source:userset;mso-width-alt:4534;width:93pt" width="124"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:5010;width:103pt" width="137"> <col style="mso-width-source:userset;mso-width-alt:6326;width:130pt" width="173"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:93pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #95B3D7; border-right:none;border-bottom:.5pt solid #95B3D7;border-left:none; background:#4F81BD;mso-pattern:#4F81BD none" height="20" width="124">Commission (%) </td> <td style="width:58pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="77">Retainer </td> <td style="width:65pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="87">Retained? </td> <td style="width:48pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">VAT?</td> <td style="width:103pt;font-size:11.0pt;color:white;font-weight: 700;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="137">Transaction Month</td> <td style="width:130pt;font-size:11.0pt;color:white;font-weight: 700;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="173">Real Commission Charge</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right" height="20">10</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">1 in 10</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">No</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">Yes</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">01/03/2012</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">1.995</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right" height="20">10</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">1 in 10</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">Yes</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">Yes</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">01/03/2012</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="20">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">
</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">01/03/2012</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" height="20">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">
</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">01/03/2012</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right" height="20">10</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">1 in 10</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">No</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">Yes</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">01/02/2012</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">2.995</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" height="20">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">
</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">01/02/2012</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" height="20">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">
</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">01/03/2012</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" height="20">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">
</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">01/02/2012</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right" height="20">10</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">1 in 10</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">Yes</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">Yes</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">01/02/2012</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" height="20">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">
</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">
</td> <td class="xl65" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">01/02/2012</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">0</td> </tr> </tbody></table>

 

[ruve1k]

Try adding a calculated column to perform the calculation row by row. Then create a measure that sums that calculated column.

 

[masplin]

Ah I see the calculated field only takes the results in the pivot table and combines them. Was trying to save columns. Thanks

 

[ruve1k]

If you really don't want to add a column, you could instead try creating the measure using the SUMX function, which essentially iterates through your table and performs the calculation row by row and then sums up the results.

E.g.

=SUMX(Table1, Table1[Commission] / Table1[Commission Pct] )

 

[masplin]

oh nice hadn't heard of that function thanks

 

[powerpivotpro]

More details on SUMX here: http://www.powerpivotpro.com/2010/02/sumx-the-5-point-palm-exploding-fxn-technique/