Sort axis values by spend of most recent year (2016)

[ronnoc]

I can sort the axis by year or by spend, is it possible to have them sort by the largest spend for the year 2016? (so Pharmaton first)

DAX formula possibly?

Any insight appreciated! 

[austinsense]

Yes, it's possible, not as easy as it should be. There are probably other ways to do this, but here's one approach to have them sort by "2016 Spend":

1. Create a "2016 Spend" measure

2016Spend:= CALCULATE([Spend], Calendar[Year] = 2016)

2. Create a "Brand Rank by 2016 Spend" measure

BrandRankby2016Spend:= RANKX( ALL(Brands[BrandName]), [2016Spend])

3. I'm actually not sure how to do the "Sort By" in Power BI but you can give it a whirl - you may have to create #2 as a Calculated Column

Here's where we cover RANKX: https://www.powerpivotpro.com/2015/06/rankx-apalooza-within-and-across-groups-with-filters-etc/

[ronnoc]

Have you tested the measure BrandRankby2016Spend? 

Could be something wrong with my dataset but it just returns 1 on every row.

Thanks for your help so far.

[ronnoc]

@austinsense Thanks for your help! Unfortunately I cant order Brand by the new calculared column brandRankBy2016 because a column can not be sorted by another column that is already sorted by the first column hahaha.

PowerBI: the difficult stuff became easy, the easy stuff became impossible

[v-shex-msft · Accepted solution]

Hi @ronnoc,

Perhaps you can use "Line and clustered column chart" to sort by rank measure.

In addition, I test on clustered column chart but haven't found a way to sort by other fields.

Regards,

Xiaoxin Sheng