Clustered Column Based on Criteria

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

SamWiseOwl
Super User
1 year ago
HI Anonymous
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.
- Anonymous
  Not applicable
  1 year ago
  Thanks. I need to filter only margin between 45% - 100%. Can I add another ampersand showing <=1?
SamWiseOwl
Super User
1 year ago
Hi Anonymous exactly, sorry I assumed it couldn't go above 100 xD
Anonymous
Not applicable
1 year ago
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
  Super User
  1 year ago
  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 🙂

Forum Discussion