Top 10 suppliers as percentage of all.

[ajm]

I am wanting to show the top 10 suppliers by spend and show their respective percentages of total sales. Pivot table easily gives me Top 10 but the options for % don't give the opportunity to compare each of the suppliers spend amounts to total sales, as opposed to % of top 10 spend. any suggestions?

 

[Misca]

Power Pivot / DAX is your best friend with this kind of calculations. If you're using Excel 2010 or later you should be good to go and you'll get away with basically these three simple calculations:

Sales:=SUM(Sales[SalesAmount])
Total Sales:=CALCULATE([Sales],ALL(Suppliers))
Pct of Total Sales:=DIVIDE([Sales],[Total Sales])

The Sales measure simply sums the SalesAmount column in your Sales table.
The CALCULATE in Total Sales removes the filters from the Suppliers-table, but you can still filter the sales table by any other related table.
The Pct of Total Sales measure returns just what the title says.

Depending on your data table structure you might have to alter the formulas a little bit but as long as you have a relationship from your Sales table to your Suppliers table - either through Products table or directly - you should be able to use some version of these formulas.

If you're not familiar with Pover Pivot or DAX you might want to watch one of the PowerPivot tutorials found in YouTube.