Calculate Highest TopN values in a dataset
The DAX language offers a function that provides the ranking of an element sorted by using a certain expression. Such a function is RANKX (), it is a scalar function and it is also an iterator.
Let’s say that we have a dataset that contains; production machine numbers, production value. we want to rank the machine according to the average of their production capacity, and extract the Top 3, 5, 10 from them. how to do it?
Create a sepearated table that contain the topN values, as showing here in this table, this table will be used as a disconnected filter to get the top 3, top5, top10 from the dataset,
Create the ranking standard, in our case let’s say that calculate the Average of prodcution, as Measure as showing;
The main idea to achieve this scenario is, create two ranks; the first depends on the slicer’s selected value, the second one’s ranking the machines according to their average production capacity, then create a measure that says; if the machine ranking is greater or equal to the slicer ranking then represent, the accordance top ranked values (Top3, Top5, Top10), as showing in the example;
The first rank; ranking the machines according to their production capacity;
In this code RANKX () first of all we’re defining the ALL values in the table, then define the ranking standard and finally the order way; ASC, DESC,
If you won’t define the all values, you will get the rank “1” for every single machine, which is reflecting the filter and row context before implementing any filter on the context,
The second rank; create a measure that will represent the selected value in the slicer as a value in the column in the report, as following;
According to the preceding code, we’re saying that, if a value being selected in the slicer, then represent the accordance value in the report, if there’s no selection being made on the filter then put the machine ranks according to the average production capacity,
After that when an active filter is being selected from the slicer, then do this comparison, if the machine ranking is greater or equal to the TopN ranking then show the associated TopN rank (Top3, Top5, Top10)
Hope this helps,
KENAN JADDENE