# SWITCH & Ranking is just amazing!

All I wanted to share this on the forum here! Think its just amazing! I really do!
I remember when I was working in the automotive industry using Tableau BI, the senior directors wanted a dynamic rank across all financial metrics of cars from cost from storage compounds, pre-sales, profits, profits last quarter and it was not easy at all.very hard and static. Using SWITCH is an absolute game changer!

I am guessing I can now even Rank on Sales LQ, just need to create a measures for Sales LQ and use inside SWITCH and RANK.!

Sam any ideas where I am going wrong.
Trying to add another columns inside the rank.

Yes great example, thanks for sharing.

Here’s another cool idea around this as well

That will enable a quicker solution on this particular one.

See attachement the query
If you go to the measure "2.Dynamic Rank.

And its only the last section of the Dax function //Region Ranking Sale. Its look perfectly fine to me.
I’ve basically said if:
metric selected = “total sale”
Rank number = 3
Rank Column = Region, give me the rank.
It doesnt not look right to me, as the city table (highlighed in blue) shows >3 results.should only be first 3.

Thanks in advance Sam for checking.

This formula seems way to complicated. Far more than it needs to be.

SIMPLIFY here.

``````2.Dynamic Rank. =
SWITCH(TRUE(),
//Customer Sales
Metric[Metric Selected] = "Total Sale" && [Rank Number] = 3 && [RankColumn] = "Customers",
CALCULATE([Total Sales],FILTER(VALUES(Customer[Customer Names]),RANKX(ALL(Customer),[Total Sales],,DESC)<=3)),

Metric[Metric Selected] = "Total Sale" && [Rank Number] = 5 && [RankColumn] = "Customers",
CALCULATE([Total Sales],FILTER(VALUES(Customer[Customer Names]),RANKX(ALL(Customer),[Total Sales],,DESC)<=5)),

Metric[Metric Selected] = "Total Sale" && [Rank Number] = 10 && [RankColumn] = "Customers",
CALCULATE([Total Sales],FILTER(VALUES(Customer[Customer Names]),RANKX(ALL(Customer),[Total Sales],,DESC)<=10)),

Metric[Metric Selected] = "Total Sale" && [Rank Number] = 15 && [RankColumn] = "Customers",
CALCULATE([Total Sales],FILTER(VALUES(Customer[Customer Names]),RANKX(ALL(Customer),[Total Sales],,DESC)<=15)),

Metric[Metric Selected] = "Total Sale" && [Rank Number] = 20 && [RankColumn] = "Customers",
CALCULATE([Total Sales],FILTER(VALUES(Customer[Customer Names]),RANKX(ALL(Customer),[Total Sales],,DESC)<=20)),

//Customer Profit
Metric[Metric Selected] = "Total Profit" && [Rank Number] = 3 && [RankColumn] = "Customers",
CALCULATE([Total Profit],FILTER(VALUES(Customer[Customer Names]),RANKX(ALL(Customer),[Total Profit],,DESC)<=3)),

Metric[Metric Selected] = "Total Profit" && [Rank Number] = 5 && [RankColumn] = "Customers",
CALCULATE([Total Profit],FILTER(VALUES(Customer[Customer Names]),RANKX(ALL(Customer),[Total Profit],,DESC)<=5)),

Metric[Metric Selected] = "Total Profit" && [Rank Number] = 10 && [RankColumn] = "Customers",
CALCULATE([Total Profit],FILTER(VALUES(Customer[Customer Names]),RANKX(ALL(Customer),[Total Profit],,DESC)<=10)),

Metric[Metric Selected] = "Total Profit" && [Rank Number] = 15 && [RankColumn] = "Customers",
CALCULATE([Total Profit],FILTER(VALUES(Customer[Customer Names]),RANKX(ALL(Customer),[Total Profit],,DESC)<=15)),

Metric[Metric Selected] = "Total Profit" && [Rank Number] = 20 && [RankColumn] = "Customers",
CALCULATE([Total Profit],FILTER(VALUES(Customer[Customer Names]),RANKX(ALL(Customer),[Total Profit],,DESC)<=20)),

//Customer Profit Margin
Metric[Metric Selected] = "Profit Margin" && [Rank Number] = 3 && [RankColumn] = "Customers",
FORMAT(CALCULATE([Proft Margin],FILTER(VALUES(Customer[Customer Names]),RANKX(ALL(Customer),[Proft Margin],,DESC)<=3)),"0.0%"),

Metric[Metric Selected] = "Profit Margin" && [Rank Number] = 5 && [RankColumn] = "Customers",
FORMAT(CALCULATE([Proft Margin],FILTER(VALUES(Customer[Customer Names]),RANKX(ALL(Customer),[Proft Margin],,DESC)<=5)),"0.0%"),

Metric[Metric Selected] = "Profit Margin" && [Rank Number] = 10 && [RankColumn] = "Customers",
FORMAT(CALCULATE([Proft Margin],FILTER(VALUES(Customer[Customer Names]),RANKX(ALL(Customer),[Proft Margin],,DESC)<=10)),"0.0%"),

Metric[Metric Selected] = "Profit Margin" && [Rank Number] = 15 && [RankColumn] = "Customers",
FORMAT(CALCULATE([Proft Margin],FILTER(VALUES(Customer[Customer Names]),RANKX(ALL(Customer),[Proft Margin],,DESC)<=15)),"0.0%"),

Metric[Metric Selected] = "Profit Margin" && [Rank Number] = 20 && [RankColumn] = "Customers",
FORMAT(CALCULATE([Proft Margin],FILTER(VALUES(Customer[Customer Names]),RANKX(ALL(Customer),[Proft Margin],,DESC)<=20)),"0.0%"),

//Product Sales
Metric[Metric Selected] = "Total Sale" && [Rank Number] = 3 && [RankColumn] = "Product",
CALCULATE([Total Sales],FILTER(VALUES(Products[Product Name]),RANKX(ALL(Products),[Total Sales],,DESC)<=3)),

Metric[Metric Selected] = "Total Sale" && [Rank Number] = 5 && [RankColumn] = "Product",
CALCULATE([Total Sales],FILTER(VALUES(Products[Product Name]),RANKX(ALL(Products),[Total Sales],,DESC)<=5)),

Metric[Metric Selected] = "Total Sale" && [Rank Number] = 10 && [RankColumn] = "Product",
CALCULATE([Total Sales],FILTER(VALUES(Products[Product Name]),RANKX(ALL(Products),[Total Sales],,DESC)<=10)),

Metric[Metric Selected] = "Total Sale" && [Rank Number] = 15 && [RankColumn] = "Product",
CALCULATE([Total Sales],FILTER(VALUES(Products[Product Name]),RANKX(ALL(Products),[Total Sales],,DESC)<=15)),

Metric[Metric Selected] = "Total Sale" && [Rank Number] = 20 && [RankColumn] = "Product",
CALCULATE([Total Sales],FILTER(VALUES(Products[Product Name]),RANKX(ALL(Products),[Total Sales],,DESC)<=20)),

//Product Profit
Metric[Metric Selected] = "Total Profit" && [Rank Number] = 3 && [RankColumn] = "Product",
CALCULATE([Total Profit],FILTER(VALUES(Products[Product Name]),RANKX(ALL(Products),[Total Profit],,DESC)<=3)),

Metric[Metric Selected] = "Total Profit" && [Rank Number] = 5 && [RankColumn] = "Product",
CALCULATE([Total Profit],FILTER(VALUES(Products[Product Name]),RANKX(ALL(Products),[Total Profit],,DESC)<=5)),

Metric[Metric Selected] = "Total Profit" && [Rank Number] = 10 && [RankColumn] = "Product",
CALCULATE([Total Profit],FILTER(VALUES(Products[Product Name]),RANKX(ALL(Products),[Total Profit],,DESC)<=10)),

Metric[Metric Selected] = "Total Profit" && [Rank Number] = 15 && [RankColumn] = "Product",
CALCULATE([Total Profit],FILTER(VALUES(Products[Product Name]),RANKX(ALL(Products),[Total Profit],,DESC)<=15)),

Metric[Metric Selected] = "Total Profit" && [Rank Number] = 20 && [RankColumn] = "Product",
CALCULATE([Total Profit],FILTER(VALUES(Products[Product Name]),RANKX(ALL(Products),[Total Profit],,DESC)<=20)),

//Product Profit Margin Ranknig
Metric[Metric Selected] = "Profit Margin" && [Rank Number] = 3 && [RankColumn] = "Product",
FORMAT(CALCULATE([Proft Margin],FILTER(VALUES(Products[Product Name]),RANKX(ALL(Products),[Proft Margin],,DESC)<=3)),"0.0%"),

Metric[Metric Selected] = "Profit Margin" && [Rank Number] = 5 && [RankColumn] = "Product",
FORMAT(CALCULATE([Proft Margin],FILTER(VALUES(Products[Product Name]),RANKX(ALL(Products),[Proft Margin],,DESC)<=5)),"0.0%"),

Metric[Metric Selected] = "Profit Margin" && [Rank Number] = 10 && [RankColumn] = "Product",
FORMAT(CALCULATE([Proft Margin],FILTER(VALUES(Products[Product Name]),RANKX(ALL(Products),[Proft Margin],,DESC)<=10)),"0.0%"),

Metric[Metric Selected] = "Profit Margin" && [Rank Number] = 15 && [RankColumn] = "Product",
FORMAT(CALCULATE([Proft Margin],FILTER(VALUES(Products[Product Name]),RANKX(ALL(Products),[Proft Margin],,DESC)<=15)),"0.0%"),

Metric[Metric Selected] = "Profit Margin" && [Rank Number] = 20 && [RankColumn] = "Product",
FORMAT(CALCULATE([Proft Margin],FILTER(VALUES(Products[Product Name]),RANKX(ALL(Products),[Proft Margin],,DESC)<=20)),"0.0%"),

//Region Ranking Sale
Metric[Metric Selected] = "Total Sale" && [Rank Number] = 3 && [RankColumn] = "Region",
CALCULATE([Total Sales],FILTER(VALUES(Regions[City]),RANKX(ALL(Regions),[Total Sales],,DESC)<=3)))
``````

Please review this tutorial here that goes through the exact technique to use for dynamic ranking.

Another one to review

Any luck on why the Region is not Ranking though? this is what i could not solve if you dont mind?
Everything else in the functions works fine just the section for Region.

There’s really no point in my advising of a solution here as the whole formula is way to complicated.

If you simplify it down to the correct technique I’m pretty confident it will become very obvious.

Really view the dynamic technique via the attached links. On a scale of complexity right now, you’re at a 10/10 when it should be at 5/10.

sure! thanks for the advice on the other comments as well Sam…
makes sense will check this out.

Shrik