Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Sergii22
Helper I
Helper I

top N by turnover in days

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?
154045.jpg154139.jpg154230.jpg
1 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.

vyaningymsft_0-1732612384699.png

 

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

View solution in original post

6 REPLIES 6
saud968
Super User
Super User

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!

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 

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!

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

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.

vyaningymsft_0-1732612384699.png

 

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

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.