Scripting or Chart

[MK_QSL]

We have below purchase data in Brands vs Supplier format…

Brand	Supplier	SUM(Purchase)
B1	A	100
B1	B	50
B1	C	150
B1	D	180
B1	E	80
B2	B	250
B2	C	40
B2	D	60
B3	C	80
B3	D	100
B3	E	20
B4	C	60
B4	D	100
B4	E	180
B5	A	130
B5	C	250
B5	D	100
B5	E	85
B6	A	80
B6	B	100
B6	D	150

For each Brand, we have particular main (prime) supplier and rest suppliers are we using only if that Brand is not available from our major supplier.

So, I want below table…

Here Others are total for non-prime supplier.

How can I achieve this…

Brand	Supplier	SUM(Purchase)
B1	A	100
B1	C	150
B1	E	80
B1	Others	230
B2	B	250
B2	C	40
B2	Others	60
B3	C	80
B3	Others	120
B4	C	60
B4	D	100
B4	Others	180
B5	A	130
B5	D	100
B5	Others	335
B6	B	100
B6	Others	230

[#2 · Accepted solution]

Hi Manish

You need to set up a table in the data that lists each Brand and their main supplier(s) (I am guessing from the above table that there could than 1?)

ie so something like

Brand	Supplier	Supplier_Type
B1	A	MAIN
B1	C	MAIN
B1	E	MAIN
B2	B	MAIN
B2	C	MAIN

Either join this to the main table or leave it floating, to be joined in the application.

Then set up the table chart as you did earlier, except do a calculated dimension for supplier:

Dimension1:Brand

Dimension2(calculated): =if(Supplier_Type='MAIN',Supplier,'Others')

Expression: =sum(purchase)

This will group any suppliers that are not the main one for that brand.

Erica

[#3]

NB Manish, as an aside it could be easier (depending on your data model) to construct a table with every permutation of Brand and Supplier, with the Supplier name or "others" as a 3rd field:

Brand	Supplier	Supplier_Group
B1	A	A
B1	B	B
B1	C	OTHERS
B1	D	OTHERS
B2	A	OTHERS
B2	B	B
B2	C	C
B2	D	D
B3	A	A

This would be easier to apply in the chart, as you can add it straight in as a dimension. The downside is that it wont account for any new suppliers that are not yet in the list. So it depends on your data a lot really!

Erica