Sorting legend by largest value

[ronnoc]

I have a % stacked column chart with

- year along the x axis, list of companies as the legend, spend amount as the value

I want the chart to be sorted by largest spend, so the largest %'s of each bar is at the bottom and then smaller towards the top. I am not able to sort the company field by spend or else i get the error

 

My only option is to create a new table with the distinct values of advertising company and a sum of the spend for each. Hopefully then I can create a measure or index to sort by.

I really need help with the DAX formula for this, I have been here for 4 hours and it feels like I have tried everything but I am very new to DAX and data analysis in general.

Lets just say here is my table.

Table 1:

Cat 1

Cat 1

Cat 1

Dog 1

Dog 2

Dog 1

Fish 1

This is the table I want to create.

Table 2 (desired output):

Fish 1

Cat 3

Dog 4

It seems so simple but I have tried using SUMMARIZE, SUMX, VALUES, which always seem to get me 90% of the way to my solution. I have found learning DAX to be the most frustrating and deceptively mind boggling language in 5 years of computer science. If anyone could recommend a good resource that would start at the basics and help me get a grasp with this I would be very very appreciative.

please help

[Judy]

Hi,

I am looking for someway to show the value of stacked column chart from largest at the bottom and going to the smallest at the top in the chart below. Could you please show me how to do?

Thank you for your advice,

[Vvelarde · Accepted solution]

@ronnoc

With Summarize you can obtain the expected

SummarizePets =
SUMMARIZE ( Table1, Table1[Pet], "Quantity", CALCULATE ( SUM ( Table1[Q] ) ) )

[ronnoc]

I swear I tried that multiple times!!

In any case I tip my hat to you good sir.