Top 10% Supplier Spend by Category

[Deadgarth]

I've been struggling to create a measure that divides the spend of the top 10 suppliers by total spend with good performance when displayed in a bar chart broken down by categories. I've searched online and on this forum and have tried various methods. Below are the measures I've found work the best, but ultimately a bar chart that displays the Top 10 % Supplier Spend by Category & Subcategory takes upwards of 1 minute to display.

I can post info from DAX Studio or whatever would be helpful to those who can assist. At this point, I'm leaning towards the design of the data model being what's holding it up. Either that or this type of calculation that uses RANKX can't be done very well with the number of suppliers in the supplier table; it has almost 300 thousand rows.

Spend =
SUM(Spend[SpendAmount])

Supplier Ranking =
RANKX(ALL('Supplier'[Supplier Name]), Spend[Spend])

Top 10 Supplier Spend =
SUMX(
    FILTER(
        VALUES('Supplier Parent'[Global Parent Supplier Name]),
        [Supplier Parent Ranking] <= 10
    ),
    Spend[Spend]
)

Have also tried:

Top 10 Supplier Spend =
CALCULATE(
    Spend[Spend],
    TOPN(
        10,
        VALUES('Supplier Parent'[Global Parent Supplier Name]),
        Spend[Spend]
    )
)

Top 10% of Total Supplier Spend =
DIVIDE([Top 10 Supplier Spend], Spend[Spend])

[Anonymous]

Spend =
SUM(Spend[SpendAmount])

Supplier Ranking =
RANKX(ALL('Supplier'[Supplier Name]), Spend[Spend])

Top 10 Supplier Spend =
SUMX(
    FILTER(
        VALUES('Supplier Parent'[Global Parent Supplier Name]),
        [Supplier Parent Ranking] <= 10
    ),
    Spend[Spend]
)

Have also tried:

Top 10 Supplier Spend =
CALCULATE(
    Spend[Spend],
    TOPN(
        10,
        VALUES('Supplier Parent'[Global Parent Supplier Name]),
        Spend[Spend]
    )
)

Top 10% of Total Supplier Spend =
DIVIDE([Top 10 Supplier Spend], Spend[Spend])

---

I'd try to do something like this. Let's say that your ranking itself works fast. Have you tried to rank all your suppliers in DAX Studio? How long does it take? It SHOULD be blazingly fast. I mean a query like this:

evaluate
calculatetable(
	summarizecolumns(
		'Supplier Parent'[Supplier Name],
		"Rank", [Supplier Ranking]
	),
	<some filters here>
)

If this is not blazingly fast, then you have to do ranking by a different means without using RANKX. You can write such ranking all by yourself using calculate and filters.

If the ranking is that fast enough, then you could do this:

[Top X Supplier Spend] =
var __topCount = 10 // this could be taken from a disconnected dimension
var __topSuppliers =
	filter(
		// this will be blazingly fast by assumption
		addcolumns(
			values( 'Supplier Parent'[Global Parent Supplier Name] ),
			"SupplierRank", [Supplier Ranking]
		),
		[SupplierRank] <= __topCount
	)
var __result =
	calculate(
		[Spend],
		__topSuppliers
	)
return
	__result

I'd personally use SupplierID (integer) instead of Supplier Name.

Best

D

[Anonymous]

Why are you using the name of a table in front of a measure? This is confusing and very BAD practice.

Best
D

[sturlaws]

Hi @Deadgarth 

not sure I have tested it on a sufficiently large dataset, but it seems to be slightly faster than the version with sumx:

top 10 supplier spend =
CALCULATE (
    SUM ( Sheet1[spendamount] );
    FILTER ( VALUES ( 'Sheet1'[supplier] ); [Supplier Ranking] <= 10 )
)

 Cheers,
Sturla