Display data in a fixed order and show data for the top three selections

[v-jingzhan-msft]

Scenario: 

Sometimes when we create a report, we may want to implement a complex data sorting in visuals according to multiple ranking rules. This is not easily done by sorting by a single column or ranking by a single expression. We will need some DAX techniques to get there.

In this article, we want to sort car brands in a table visual according to several rules. And when a ranking is selected, a dynamic display includes the two car brands prior to the selected ranking.

Expected Result:

1. Make the data displayed in a fixed arrangement:

- Top 3 cars from Germany by sales

- Top 3 cars from rest of the countries by sales (and is not Toyota)

- Toyota

- Top 1 car by growth (is not top 3 from Germany, is not top 3 from rest of countries and is not Toyota)

2. When a ranking is selected, the display includes the two car types preceding the selected ranking.

Sample Data:

Here is the sales table, including car, country, sales and growth.

How:

1. Add a Colum to sort sales in Germany and other countries except Toyota.

Rank_Sales = 
IF (
  'Table'[Car] = "Toyota",
     BLANK (),
     IF (
         'Table'[Country] = "Germany",
         RANKX (
             FILTER ( 'Table', 'Table'[Country] = EARLIER ( 'Table'[Country] ) ),
             'Table'[Sales]
         ),
         RANKX ( FILTER ( 'Table', 'Table'[Country] <> "Germany" ), 'Table'[Sales] )
     )
 )

2. Add a Flag column as a Filter to filter cars that are not included in the condition.

Flag = 
VAR _rank_growth =
     IF (
         'Table'[Rank_Sales] > 3
             && 'Table'[Car] <> "Toyota",
         RANKX (
             FILTER ( 'Table', 'Table'[Rank_Sales] > 3 && 'Table'[Car] <> "Toyota" ),
             'Table'[Growth]
         ),
         BLANK ()
     )
 RETURN
     IF (
         'Table'[Rank_Sales] <= 3
             || _rank_growth = 1
             || 'Table'[Car] = "Toyota",
         1,
         0
     )

3. Add a Rank_Result column to sort the car brands in the correct order and filter cars that are not included in the condition.

Rank_Result = 
VAR _Top3exceptToyota =
     IF ( 'Table'[Rank_Sales] <= 3, 'Table'[Rank_Sales], 0 )
 VAR _RankTop6 =
     IF ( 'Table'[Country] = "Germany", _Top3exceptToyota, _Top3exceptToyota + 3 )
 VAR _Toyota =
     IF ( 'Table'[Car] = "Toyota" , 7, _RankTop6 )
 RETURN
     IF ( _Toyota = 0, 8, _Toyota )

4. Take the Rank_Result column as Table 2 that is not related to Table and put it into the slicer. Add a measure to implement a dynamic display that includes the two car brands of the selected ranking when the ranking is selected.

3Cars = 
VAR _SelectedRank_Result =
     MAXX ( ALLSELECTED ( 'Table 2'), 'Table 2'[Rank_Result] )
 RETURN
     IF (
         MAX ( 'Table'[Rank_Result] ) >= ( _SelectedRank_Result - 2 )
             && MAX ( 'Table'[Rank_Result] ) <= _SelectedRank_Result,
         SELECTEDVALUE('Table'[Car]),
         BLANK ()
     )

This is our expected result:

Summary:

In some cases, if a fixed sort is required, the data can be sorted in ascending order after a custom sort. Then when a ranking is selected, a dynamic display includes the two car brands prior to the selected ranking.

Hope this article will help people with similar questions.    

Author: Yifan Wang

Reviewer: Ula and Kerry

Display data in a fixed order and show data for the top three selections.pbix