
1. Re: Calculation Problems  Distance From Average
Norbert Maijoor Nov 16, 2018 7:31 AM (in response to Marc Monteys)Hi Marc
Find my approach as reference below and stored in attached workbook version 2018.2 located in the original thread
1. M1. Cound Ord ID: countd([Order Id.])
2. M2. Qty Orders per Customer:
if size()<>1 then sum({fixed [Customer Name]:([M1. Cound Ord ID])})
elseif size()=1 then sum({fixed :([M1. Cound Ord ID])})
END
3. M3. % count:
if size()<>1 then [M1. Countd Ord ID]/[M2. Qty Orders per Customer]
elseif size()=1 then [M1. Countd Ord ID]/[M2. Qty Orders per Customer]
END
Hope it helps,
Regards,
Norbert

2. Re: Calculation Problems  Distance From Average
Marc Monteys Nov 17, 2018 9:05 AM (in response to Norbert Maijoor)Hi Norbert,
thank you for the help.
What I'm trying to get 5 calculations:
 C1 : (works fine in both data sources) Qty Ord :  1  Quantity of orders. Easy in this case coz 1 row = 1 order so calculation is just a duplicate of "number of rows"
 C2 : (works fine in both data sources) Total Ord. Of Customer :  {FIXED [Customer Name]:SUM([Qty Ord.])}  Total orders af a customer no matter what product it is.
 C3 : (works fine in both data sources) % Qty / Total Customer :  SUM([Qty Ord.])/SUM([Total Ord. Of Cust.])  % of what the qty of orders (C1) represents on total of orders of that client (C2) , that is C1/C2
 C4 : (works fine just in superstore!!!!! ) Avg of Cust. % :  {FIXED [Product Name]:AVG([Qty Ord.]/[Total Ord. Of Cust.])}  The average of all values found in previous calculation C3 by product. That is:
 (Superstore) 1.7 Cubic Foot Compact "Cube" Office Refrigerators = 3,72
 (Superstore) Avanti 1.7 Cu. Ft. Refrigerator = 7,07
 (Excel) Product1 = 49,44
 (Excel) Product2 = 62,92
 C5: Distance From Avg:  SUM([Avg Of Cust. %])[% Qty / Total Cust.]  Distance beetwen C3 from C4 to show which customers have an unusual qty of order of a particular product versus the rest of the customers.
The point is that all my calculation work fine with superstore data source but C4 doesn't works fine with Excel data source (now is 16,39 but it should be 49,44)
Any idea why?