Getting A Count From Multiple Dimensions

[#1]

HNY folks...

Got a painful task that's proving to be a bit of a pig to solve...

Basically:

I have a straight table that has 3 dimensions and an expression..

OrderID	Warehouse	Customer	Total Order Units
1	Edinburgh	Smith	77.00
1	Edinburgh	Jones	77.00
1	Edinburgh	Brown	77.00

Ignore the logic of the data, the basic premise is that one order can sit in one warehouse, but have multiple customer names.

The expression total (77.00) is the total order value - what I need to do, is to assume it's a straight split, so basically have the 77 split equally between all the Customers in the order (25.67)...given other orders will exist that have more (or less) customers, I need an expression that will count the customers per order per warehouse and divide the total order value....

Confused ?  Welcome to the club.

Thanks in advance....

(Don't ask for a QVW as there isn't one )

[Clever_Anjos]

count(total <OrderId> Customer) will bring you how much customers are in your Order, so you can divide your Total Unit Units by this value

[kiranmanoharrode]

Hi Graeme,

Use AVG() function for above requirement.

=AGGR(AVG(Order_Units),Customer,Warehouse)

Regards,
Kiran

[#4 · Accepted solution]

Thanks Clever

Nearly - but with a bit of tweaking I got there..

Count(TOTAL <Warehouse>Customer)

Your answer was limited to only that selection (ie OrderID 1 & Warehouse Edinburgh) - when you expanded back out to the full order id history, the count expanded to.

Thanks though - can't believe I missed that