Forum Discussion
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 ranking should be recalculated to ignore any periods that are not selected.
Users can select any combination of fiscal periods. This selection can include continuous periods (e.g., 2024 P05, 2024 P04, 2024 P03) or non-continuous periods (e.g., 2024 P04, 2023 P11, 2022 P03).
The ranking should dynamically adjust based on the selected periods. For example, if the user selects 2024 P05, 2023 P05, and 2022 P05, the ranks should be recalculated as 1, 2, and 3 based on the values for these periods alone, not the entire dataset. This ranking should ensure that the top 5 and bottom 3 values are identified correctly based on the user's selection.
To represent the top 5 and bottom 3 values I will use conditional formatting, so after ranking the periods correctly, it should return 1 for the top 5 values and 2 for the bottom 3.
The fiscal periods are stored in the Calendar[Year Period] column.
The format of fiscal periods is YYYY PXX, for example, 2024 P05 for the 5th period of the year 2024.
We have a Fiscal Calendar Table that has columns for Year, Beginning Date, Ending Date and Period Number.
We have measures for all KPIs.
Is this possible?
6 Replies
Thank you!! It was that ALLSELECTED that was missing
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
Hello llealsantos , remember to adhere to the decorum of the Community Forum when asking a question.
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.Unfortunately, I don't have time to do all that. Thank you for your useful reply.
