Clustered Column Based on Criteria

[tksnota]

Hi,

I want to display the top 10 of the projects via clustered column based on YTD% ranging 45%-100% with YTD Margin ranging from 2k - 1m

[tksnota]

i forgot to mention the YTD Margin and YTD% are both measures. so i tried the dax but i couldn't see these 2.

[SamWiseOwl · Accepted solution]

Tweaked it to exclude rows from showing final rank:

Top 10 within range only =

var matchingrows = 

Filter(AllSelected(table), [YTD%]>= 0.45 && [YTD%]<= 1 && [YTD margin]>= 2000 && [YTD margin]<= 1000000)

var curr = [YTD%]

Return
if(
[YTD%]>= 0.45 && [YTD%]<= 1 && [YTD margin]>= 2000 && [YTD margin]<= 1000000

,RankX(matchingrows , [YTD%],,Desc, Skip)

,BLank()
)

Add this into the visual to check it works and then use it as a filter 🙂

[SamWiseOwl]

Hi @tksnota  exactly, sorry I assumed it couldn't go above 100 xD

[SamWiseOwl]

HI @tksnota 

Create a measure using RankX that tests your conditions and then use this in the Fitler Pane or in the visual

Top 10 filter =

RankX(
Filter(AllSelected(table), [YTD%]>= 0.45 && [YTD margin]>= 2000 && [YTD margin]<= 1000000

,[YTD%],,Desc, Skip)

Either way apply a less than or equal to 10 filter.

[tksnota]

Thanks. I need to filter only margin between 45% - 100%. Can I add another ampersand showing <=1?