SWITCH & Ranking is just amazing!

Shrik · October 23, 2018, 1:31pm

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.!

Shrik · October 23, 2018, 2:59pm

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

sam.mckay · October 23, 2018, 9:18pm

Yes great example, thanks for sharing.

Here’s another cool idea around this as well

sam.mckay · October 23, 2018, 9:19pm

Can you please add the file to the forum post.

That will enable a quicker solution on this particular one.

Shrik · October 23, 2018, 10:29pm

See attachement the query
Please see page 4.
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.

2.EnterpriseDNA.ScenarioAnalysis DynamicRanking.pbix (615.3 KB)

sam.mckay · October 24, 2018, 10:07pm

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.

sam.mckay · October 24, 2018, 10:08pm

Another one to review

Shrik · October 24, 2018, 10:45pm

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.

sam.mckay · October 24, 2018, 11:52pm

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.

Shrik · October 25, 2018, 12:21am

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

Shrik