Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
This is my first post, though I have been visiting for a little while.
I have an issue that I hope somebody can help with and hopefully improve my understanding at the same time.
I am trying to find the frequency of basket sizes against a set of sales. I have a unique identifier for a sale as [Sales invoice] and I count the unit quantity by line item (product) as [Units Sold].
e.g.
[Sales invoice] | [SKU] | [Units Sold] |
---|---|---|
INVOICE123 | PRODUCT1 | 3 |
INVOICE123 | PRODUCT2 | 1 |
INVOICEABC | PRODUCT3 | 7 |
INVOICE999 | PRODUCT2 | 4 |
My attempt to find the above is to use the following:
AGGR(SUM([Units Sold]), [Sales invoice])
However this results in inaccurate quantities. Where the above should show (Basket Size 4 = Frequency 2, Basket Size 7 = Frequency 1), instead it is showing inflated numbers which do not seem to match anything I can find.
Hopefully I have explained that clearly, any help would be much appreciated.
Thank you in advance.
Michael
Try this as calculated dimension: =aggr(sum(total [Units Sold]),[Sales invoice])
And this expression: count(distinct SKU)
Try this as calculated dimension: =aggr(sum(total [Units Sold]),[Sales invoice])
And this expression: count(distinct SKU)
Hi Gysbert,
I removed "total" in your expression above as it was not present in your example and it worked!
Thank you for your assistance.
Regards,
Michael