Show number of vendors that account for 80% of spend in each product category

[Flash]

Hi Experts,

I'm fairly new to Power BI and recently started using DAX formulas. I was hoping you could help me with the following. My table looks like this.  Basically showing order lines per vendor, with a category and spend.

Vendor	Product_Category	Spend
Vend 1	A	5
Vend 1	A	10
Vend 2	A	25
Vend 3	A	60
Vend 1	B	10

I would like to create a measure where I can see the number of vendors that account for 80% of the total sales for one specifc Product category (A,B,...). So In Product Category A it should be 2 vendors. In product Category B it would be 1 vendor.

My approach was the following:

1. Create a table that shows the total spendamount per vendor per category:

Vendor	Product_Category	SpendAmount
Vend 1	A	15
Vend 2	A	25
Vend 3	A	60
Vend 1	B	10

Table = Summarize(
Allselected('Data');
'Data'[Supplier Name];
'Data'[Category];
"SpendAmount";SUM('Data'[Spend]))

Now I would also like to bring in the TotalAmount per Category:

Table = Summarize(
Allselected('Data');
'Data'[Supplier Name];
'Data'[Category];
"SpendAmount";SUM('Data'[Spend]);"CategorySpendAmount";CALCULATE(SUM('Data'[Spend]);'Data'[Category])

However, I always get an error, that: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed. Could you please explain?

My next steps would then be to create a new column with percentage of Spend Amount of CategorySpendAmount and Rank the values by Group, as well as adding a calculation on count of suppliers that add up to 80% of that ration.

Any help is appreciated. Thanks

[Anonymous · Accepted solution]

I have not checked the below yet but you may check it yourself. Let me know should you have any problems. T is your table.

[Total Spend] = SUM( T[Spend] )

[# Vendors 80%+] =
var __atLeast = 0.8
var __atMostButStillLessThan = 1 - __atLeast
// Calculating the number
// of vendors that collectively
// account for at least 80%
// of total spend within the current
// filter context. The ordering is from the highest-spend
// vendor to the lowest-spend. But the calculation
// proceeds in a different direction - from the
// smallest amount to the highest. First, we want to
// calculate the number of vendors for which the
// cumulative amount is less than 20%. Once we have
// this number, we can subtract it from the count of
// all the vendors in the current context.
var __totalSpend = [Total Spend]
var __vendorsWithAmounts =
	ADDCOLUMNS(
		VALUES( T[Vendor] ),
		"@TotalSpend", [Total Spend]
	)
var __vendorsAccountingForAtMostButStillLessThan =
	filter(
		__vendorsWithAmounts,
		var __currentVendor = T[Vendor]
		var __currentAmount = [@TotalSpend]
		var __spendUpToCurrentVendor =
			SUMX(
				filter(
					__vendorsWithAmounts,
					[@TotalSpend] < __currentAmount
					||
					(
						[@TotalSpend] = __currentAmount
						&&
						T[Vendor] <= __currentVendor					
					)
				),
				[@TotalSpend]
			)
		return
			__spendUpToCurrentVendor < 
				__atMostButStillLessThan * __totalAmount
	)
var __count =
	DISTINCTCOUNT( T[Vendor] )
	- COUNTROWS( __vendorsAccountingForAtMostButStillLessThan )
RETURN
	__count

Best

D

[jaweria_khan]

Hello. 
What does the __totalAmout measure represent here?
__spendUpToCurrentVendor < __atMostButStillLessThan * __totalAmount

is it the same as total spend? 

[Anonymous]

Please read this:

https://www.sqlbi.com/articles/filter-arguments-in-calculate/

Incidentally, your task has not been fully logically described. You have not stated what you mean by "account for 80% of spend." What happens if you have 2 vendors with the same amount? Of course, this does not matter for the calculation of counts but it will matter once you start calculating other measures. For instance, if you want to return a string with the vendors. Which one should you count in and which one to exclude? What is the order of vendors you want to include? From the smallest amount to the highest or the other way round?

Please bear in mind as well that it's not necessary to impose a restriction that you want to find the number within a particular category. If a measure is correctly written, slicing by category will give you what you want but even without any filters on category, the measure should return the number of vendors that account for (at least!) 80% of spend within all categories.

Best
D