top N by turnover in days

[Sergii22]

Dear experts, I need your help with the TOP N task
I have a table of tractor sales and turnover in days.
Task 1: It is necessary that when selecting TOP from Top Selection = {1,2,3,4,5}, the entire list of unique series with the corresponding turnover values ​​is displayed. For example, when selecting TOP 2, all unique tractor series with turnover values ​​of 8 and 9 are displayed, etc.
Task 2: Since there are repetitions in the list of series, it is necessary to create a measure that allows you to display the number of repetitions of the series. For example, Kubota B-92 - 8 days - 2. This means that this series is turned over in 8 days and there are two such sales in the list.
I created a measure, but it does not work correctly. 

Top N Turnover Series =

VAR TopSelected = SELECTEDVALUE('Top Selection'[Value], 5)  -- Get the number of positions in the top, by default 5

VAR FilteredTable =

    FILTER(

        ALL('Sale'),

        'Sale'[Series] <> "OTHERS"

    )

VAR UniqueTurnoverValues =

    DISTINCT(SELECTCOLUMNS(FilteredTable, "Turnover", 'Sale'[Turnover]))

-- Get the top N unique Turnover values

VAR TopUniqueTurnoverValues =

    TOPN(

        TopSelected,

        UniqueTurnoverValues,

        [Turnover],

        ASC

    )

-- Check that the current Turnover value is in the top N values

VAR CurrentTurnover = SELECTEDVALUE('Sale'[Turnover])

RETURN

IF(

    CurrentTurnover IN TopUniqueTurnoverValues,

    CurrentTurnover,

    BLANK()

)

As a result, I get the correct operation of the TOP filter, but not all unique values ​​are displayed.

Does anyone have any ideas on how to fix this?

[saud968]

Task 1: Display Top N Unique Series with Corresponding Turnover Values
Your current measure is almost there, but it needs a slight adjustment to ensure all unique values are displayed. Here's an updated version:

Top N Turnover Series =
VAR TopSelected = SELECTEDVALUE('Top Selection'[Value], 5)
VAR FilteredTable = FILTER(
ALL('Sale'),
'Sale'[Series] <> "OTHERS"
)
VAR UniqueTurnoverValues = DISTINCT(SELECTCOLUMNS(FilteredTable, "Turnover", 'Sale'[Turnover]))

-- Get the top N unique Turnover values
VAR TopUniqueTurnoverValues = TOPN(
TopSelected,
UniqueTurnoverValues,
[Turnover],
ASC
)

-- Filter the original table to include only rows with the top N turnover values
VAR FilteredTopNTable = FILTER(
FilteredTable,
'Sale'[Turnover] IN TopUniqueTurnoverValues
)

RETURN
SUMMARIZE(
FilteredTopNTable,
'Sale'[Series],
'Sale'[Turnover]
)
Task 2: Count the Number of Repetitions for Each Series
To count the number of repetitions for each series, you can create a separate measure:

Series Repetitions =
CALCULATE(
COUNTROWS('Sale'),
FILTER(
ALL('Sale'),
'Sale'[Series] = SELECTEDVALUE('Sale'[Series]) &&
'Sale'[Turnover] = SELECTEDVALUE('Sale'[Turnover])
)
)
Putting It All Together
You can now create a table visual in Power BI and add the Series, Turnover, and Series Repetitions measures to it. This will display the top N unique series with their corresponding turnover values and the count of repetitions.

Example
If you select Top 2, the table might look like this:

Series Turnover Series Repetitions
Kubota B-92 8 2
John Deere X 9 1
This setup should help you achieve the desired results.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Sergii22]

Thank you for your suggestion. But unfortunately the first measure gives an error - "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." Perhaps it will be more convenient if I give a link to the file in which I configure the measures. You will be able to see all my options. Thank you in advance for your time for me!

https://drive.google.com/drive/folders/171PFfOQhxcKrYz8xDPRHZ-dwa0XukdtE?usp=sharing 

[saud968]

Thank you for sharing the file, I will try to work on it as early as possible, however, please expect some delay. 

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Sergii22]

Thank you I hope you will have time to help me as quickly as possible!

[v-yaningy-msft · Accepted solution]

Hi, @Sergii22 

Thanks for saud968's reply. Your table is missing the filter for the field Turnover, you can re-filter it according to the method in the screenshot.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Sergii22]

Thank you very much, you helped a lot, your solution turned out to be simple and correct!