Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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
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