// 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
