Value based on Max value of subcategory in another column.

[jazking123]

Good morning,

I thought this would be simple but am struggling with it. I have a table similar to the one below with Order#, Category and sales. I would like to create a column which shows most popular category based on sales.

I can get max sales column with:

MaxSales = CALCULATE(MAX(Terence[sales]),ALLEXCEPT(Terence,Terence[Order#]))

However mapping that to the corresponding category name is where I'm struggling.

Ideal Outcome

Order#	category	sales	MaxSales	Ideal Answer
abc	bars	$10.	14	coffee
abc	ic	$12.	14	coffee
abc	coffee	$14.	14	coffee
xyz	bars	$16.	18	coffee
xyz	coffee	$18.	18	coffee
def	seasonal	$20.	22	beverage
def	beverage	$22.	22	beverage
def	pizza	$20.	22	beverage
def	meals	$18.	22	beverage

Power BI link 

This result solves it but doesn't specify how he does it without creating a new table. 

[Anonymous · Accepted solution]

// Please note that the code below does not
// use CALCULATE to avoid context transitions
// since this would be detrimental to speed.
// By the way, such calculations SHOULD be
// performed in Power Query, not in DAX. DAX
// is not a data mashup language. M is.

Highest Spend Category = // calc column
var __order = T[Order #]
var __categoriesWithAmounts =
	generate(
		summarize(
			// Get all the rows for the
			// current order number.
			filter(
				T,
				T[Order #] = __order
			),
			// Get all the categories
			// present in the order.
			T[Category]
		),
		// For each category get the
		// total for it within the order.
		var __cat = T[Category]
		var __amount =
			sumx(
				filter(
					T,
					T[Order #] = __order
					&&
					T[Category] = __cat
				),
				T[Sales]
			)
		return
			// Return the amount as an
			// anonymous table.
			{__amount}
	)
var __highestSpendCategory =
	// Have to use an aggregating function
	// because it might happen that 2 different
	// categories have the same total and we
	// can only return one value. We take the
	// last value in the alphabetical order. Change
	// this to MINX to get the first category 
	// in the alphabetical order.
	MAXX(
		topn(1,
			__categoriesWithAmounts,
			[Value], desc
		),
		T[Category]
	)
return
	__highestSpendCategory

Even though I've given you the DAX code, you should not do it this way. Please use the right tool for the job: Power Query. Executing DAX in a fact table (as a calculated column), which could potentially consist of tens of millions of rows or more, is a VERY BAD IDEA. For many reasons.

Best

D

[jazking123]

Thank you @Anonymous, this is amazing.

I appreciate it's terrible form, however I was given the specific request to create a calculated column. I thought it would be a simple as and didn't realize how complicated it would be.

Also thank you for include comprehensive comments.

Regards,

Gareth.

[Greg_Deckler]

Seems like you want this: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434