Average selling price Calc

[AH]

Hi,

I have a requirement like this: Calculate Average selling Price of Instruments. I am using expression like this but its giving me wrong Average.

=num(Sum({<ItemID={'XT, GT'}>} [TotalLineAmountInReportCurrency])/Count({<ItemID={'XT', GT},Qty>}Distinct InvoicesourceCustId),'$##,##0')

I am Having Problem putting the Qty in the expression.  But i have to count/sum the Qty to get the average price.

Count/sum for quantity? Any feedback would be a great help.

Thnaks,

Shan

[#2]

use avg(aggr(yourexpression,fields))

[anbu1984]

Are you trying to do this calc?

(Sum(Amt)/Count(CustID))/ Sum(Qty)

Is XT and GT two different ItemIDs? Then add quotes around each ItemID like below

=num(

(Sum({<ItemID={'XT', 'GT'}>} [TotalLineAmountInReportCurrency])/Count({<ItemID={'XT', 'GT'}>}Distinct InvoicesourceCustId)) / Sum({<ItemID={'XT', 'GT'}>}Qty)

,'$##,##0')

[AH]

Hi Santosh,

Thanks for your reply but I dont get it what you said.

Thanks,

Shan

[AH]

The expression looks convincing but its not working.

Thanks,

Shan

[anbu1984]

Can you post sample data and expected output

[AH]

Hi Anbu,

Thanks for your reply.

I need to calculate the average for (CCP and CSA combination) , and (X1 and X25 Combination) . Please find the attach data file.

The expected average for 1st combi should be around 1500 and for the second combo should be around 5000. But Thats just an idea i got from BA. Its not the exact figure any ballpark range should be fine if the calculation is ok.

Thanks,

Shan

[#8]

Hi ahmed

quick question, what does Qty measures as I would assume that we looking at quantity of items, but you have negative values in your data source, Once I understand that I bliv we can come up with an appropriate formula

Kind Regards

Dlamini

[AH]

Hi Dlamini,

Qty is the Quantity. Dont worry about the negative amount. Just consider them as Just number.

Thanks,

Shan