5 Replies Latest reply on May 31, 2016 3:49 AM by Steve Taylor

# How to show a list of numbers of POS which sold 50,49,48,...,1,0 products

Hello!

I have a list of points of sales and a list of products (50 items). Points of sales (POS) belong to different distributors. Each POS must sell all the products from the list each month, but in fact it isn't always like that. So I would like to show the number of points which sold 50, 49,48,..,1,0 products from the list. This should look like a table with the following dimensions and measures:
Distributor 1| "Number of POS with 50 products"| [Actual number of POS with 50 products]

Distributor 1| "Number of POS with 49 products"| [Actual number of POS with 49 products]

...

Distributor 1| "Number of POS with 0 products"| [Actual number of POS with 0 products]

Distributor should be chosen by filter.

By now I have only come up with the idea to use a parameter that defines the number of products that POS actually sold. But this solution allows me to show only 1 string (while I need a table with 50 strings):

Distributor 1| "Number of POS with N products"| [Actual number of POS with N products, that is calculated based on the value of the parameter ]

The calculation of [Actual number of POS with N products] is the following:

{INCLUDE [POS_ID]:

(IF COUNTD([ProductID])=[Parameter.Number of products sold] THEN 1 ELSE 0 END)}

Maybe I can somehow modify a parameter to make is show all the values (1-50) it has?

Thank you!

• ###### 1. Re: How to show a list of numbers of POS which sold 50,49,48,...,1,0 products

Hello Liliya,

Do you have an anonymised dataset you can share? It'd make it much easier to assist you.

Many thanks

Steve

• ###### 2. Re: How to show a list of numbers of POS which sold 50,49,48,...,1,0 products

Hello Steve,

Sure. Please find an Excel document attached. In order not to overload you with the data, I chose 1 distributor. POSID - ID of its points of sales, ProductID - ID of 50 products that a point must sell, SellOut=Sales.

Thank you!

• ###### 3. Re: How to show a list of numbers of POS which sold 50,49,48,...,1,0 products

Hello Liliya

Sorry for the delay, it was bank holiday here yesterday so no work for us!

I think I have a solution. Rather than define 1-50 in a fixed parameter/list I used this calculation and made it a dimension:

{FIXED [Posid]: COUNTD([Product ID])}

This, for any given distributor, provides the number of POS with N products, it's called "Count Products" in the attached workbook.

Then, I used COUNTD([Posid]) for the "actual number of POS with N products" (called "Count POS with N Products" in the workbook) and put those two columns together in a table in the workbook.

I added your parameter based calculation for comparison (called Liliya actual number POS calc).

I hope this works when you apply it to your whole dataset, please get in touch if it doesn't.

Thanks

Steve

• ###### 4. Re: How to show a list of numbers of POS which sold 50,49,48,...,1,0 products

Thank you very much, Steve!

I adapted calculations a bit for my case (added Period to LOD and counted only POS which had sales>0) and it works! Very smart solution!

• ###### 5. Re: How to show a list of numbers of POS which sold 50,49,48,...,1,0 products

You're welcome!