Calculating percentage based on multiple top N parameters

[manish2k]

HI ,

I have a table with supplier name , their category and their spend . I need to create a visual table which will show me the fragmentation of supplier spend across these categories . The output I am looking for in the visual is something like this :

Category	Top 3 Suppliers contribution to overall category spend	Top 5 Suppliers contribution to overall category spend	Top 10 Suppliers contribution to overall category spend
A	75%	85%	100%
B	55%	65%	70%
C	20%	25%	30%

Any ideas on how to go about this ?

[Phil_Seamark · Accepted solution]

Hi @manish2k

Try creating a calculated table that summarises the data using this code

Summary Table = SUMMARIZE(
                    'Table1',
                    Table1[Supplier Name],
                    'Table1'[Category] ,
                    "Total Spend",SUM('Table1'[Spend]) 
                ) 

Then add the following calculated column to this newly created table

Supplier Category Rank = 
	CALCULATE(
		COUNTROWS('Summary Table'),
		FILTER('Summary Table',
			'Summary Table'[Category] = EARLIER('Summary Table'[Category]) 
			&& 'Summary Table'[Total Spend] >= EARLIER('Summary Table'[Total Spend]
				)
				)
				)+0

Finally create the following measure

Top 3 Suppliers contribution to overall cat spend = 

VAR TopNValue = 3 
VAR Top3Spend = CALCULATE(
 SUM('Summary Table'[Total Spend]),
 FILTER(
 ALLEXCEPT('Summary Table','Summary Table'[Category]),
 'Summary Table'[Supplier Category Rank]<=TopNValue)
 )
VAR TotalSpend = CALCULATE(SUM('Summary Table'[Total Spend]),ALLEXCEPT('Summary Table','Summary Table'[Category]))
RETURN DIVIDE(Top3Spend,TotalSpend) 		

and format the measure to be a percentage.  I think this might be close 🙂

If it's close, just clone the final measure 2 times for the other TopN values

[manish2k]

@Phil_Seamark I have one change to ask , what if the main summary table gets one more filer . Let say a department . How will i be able to incorporate it into the below code?

[manish2k]

Awesome ! This worked ! Thanks a million !