Forum Discussion
Solved
Rank function on selected dates
We need to apply conditional formatting to identify the top 5 and bottom 3 values in a dataset, based on user-selected fiscal periods. The selection can include non-continuous fiscal periods, and the...
please check the rankx function what I have used on the dataset.
Please check the final output.
If you think that this helps you then accept this as your solution.
Hi,
I do not know how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
RANK function (DAX) - DAX | Microsoft Learn
WINDOW function (DAX) - DAX | Microsoft Learn
Sales: =
SUM(sales[sales])
Rank sales: =
RANK (
SKIP,
SUMMARIZE ( ALLSELECTED ( sales ), sales_team[sales_team] ),
ORDERBY ( CALCULATE ( SUM ( sales[sales] ) ), DESC )
)
Sales rank top5 and bottom3 =
VAR _top5 =
WINDOW (
1,
ABS,
5,
ABS,
SUMMARIZE ( ALLSELECTED ( sales ), sales_team[sales_team] ),
ORDERBY ( CALCULATE ( SUM ( sales[sales] ) ), DESC )
)
VAR _bottom3 =
WINDOW (
1,
ABS,
3,
ABS,
SUMMARIZE ( ALLSELECTED ( sales ), sales_team[sales_team] ),
ORDERBY ( CALCULATE ( SUM ( sales[sales] ) ), ASC )
)
VAR _teamlist =
UNION ( _top5, _bottom3 )
RETURN
CALCULATE (
SUM ( sales[sales] ),
KEEPFILTERS ( sales_team[sales_team] IN _teamlist )
)
Thank you so much!! I was missing that ALLSELECTED