Chart of top 5 categories by combined $ regardless of # of transactions comprising category value

[marshallthomson]

Hi all,

I'm trying to create a chart showing the top 5 categories by combined value, regardless of how many transactions make up the combined value. Right now when I put a top 5 filter on the visual, it shows the values for the top 5 transactions grouped by category, but these are often spread across only 3 or 4 categories. 

Do I have to pre-aggregate this data in a table in SQL or elsewhere in PBI before creating this chart? Or is there a way using different filtering or DAX that I can achieve this?

Cheers

[marshallthomson · Accepted solution]

Never mind, Chat GPT helped. It suggested making a combined value measure by summarising to a table first, then iterating over that with a SUMX:

CombinedCategoryValue = SUMX(SUMMARIZE('Data', 'Data'[Category], "Category", SUM('Data'[Value])), [Category])

Then, applying the top N filter to this field (and 'by' this field too) gives the correct number of categories in the visual.