Count according to rank

[harveenkaur]

I have the following data which shows sales and growth for 3 products with respect to different cities. I need to find the count of cities in which product A is number 1 (according to growth) with respect to other products.

** Growth has been calculated separately  by using formula (Current Year Sales-Previous Year Sale)*100/Previous Year Sales.

City	Product A Sales	Product A Growth	Product B Sales	Product B Growth	Product C Sales	Product C Growth
A1	75274	86	193401	1	46683	61
A2	126887	88	157545	61	196406	40
A3	126043	73	64573	86	131707	70
A4	120066	49	53935	97	163663	18
A5	215567	59	108608	89	15006	100
A6	216918	83	110127	91	74523	59

I want to show output in sepearate KPI .

Output should be like:-

No. of cities in which Product A is no. 1:- 2

No. of cities in which Product B is no. 1:- 3

No. of cities in which Product C is no. 1:- 1

please suggest any appropriate approach.

[Kushal_Chawda]

Based on above data what should be the expected output? Where you want to show that?

[harveenkaur]

I want to show output in sepearate KPI .

Output should be like:-

No. of cities in which Product A is no. 1:- 2

No. of cities in which Product B is no. 1:- 3

No. of cities in which Product C is no. 1:- 1

[Kushal_Chawda]

what is the definition of No 1 product? Based on Growth, how you are counting cities 2 product A?

[sunny_talwar]

Do you have multiple columns for Product (Product A, Product B, etc) or do you have a single Product column which contains values as Product A, Product B,... etc?

[harveenkaur]

 Multiple columns for Product

[harveenkaur]

No. 1 means the cities where growth is maximum for product A(w.r.t to product B and product C)

[sunny_talwar]

Can you transform your data using The Crosstable Load so that you have a single column for Products?

[Kushal_Chawda]

Please provide some sample data to work with