Show Top N values in Table

[amotto11]

Hello, I am pretty new to Power BI and Dax, and need some help showing the Top N values in a table as well as on my page.

I have a fact table with all of the product identifiers and amounts, and a dimension table with the product identifiers and their respective names. I have created another table in Power BI called Top Table with column TopN and values 5, 10, 15, 20, 100.

In my fact table i have summed the amounts using Total_Amount = sumx(Results, Results[Amount]) then i have a rank function to rank the products by amount Rank Amount = rankx(allselected('Unique Codes'[Product Code],[Total Amount],,Desc). This is working as it should ranking the products by amount. Now i would like to have a slicer that shows by TopN column where i can choose one of the values and the table as well as any graphs i have on the sheet only show the TopN values. So if i choose 5, the table would show me the top 5 products by amount, and any graph would be filtered to only the top 5 as well. Then top 10 would show the top 10 etc.

I have read a lot of things, but cannot seem to get it to work for me. It would make sense that i would want to create a virtual relationship between my fact product table and my new topN table on the rank, but obviously my TopN table does not have all of the ranks in it.

Thanks for any help.

[Sean · Accepted solution]

http://www.daxpatterns.com/parameter-table/

To see an example go to the bottom of the page at the above link where it says Downloads

Download the Excel 2013 zip files - extract all

Then Open PBI - File - Import - Excel Workbook Contents - Select ParametersTable - TopN - Excel 2013.xlsx - Open

click Start on the warning "We don't work directly with Excel... blah blah..." - Copy Data - Migration Completed - Close

Save the File as pbix - you may have to close and open it again

Now create a Table - place Product and TopSalesAmount in the table visual

then create a Slicer with the field from the Tp table - select any value from the slicer and see how the Measure reacts!

Hope this helps!

[amotto11]

I have it working on my data, but I would like to do something more, if you could help, that would be great.

I have five tables right now: Main Agency, Agency, Location, Results, and Top (created from last response)

Main Agency contains all of the major agencies and their codes. Agency contains all of the major agencies and each of the branches codes. Location table lists all of the branches and their address (full address). Results is the amount that each agency collects. Some agency locations have not collected anything, so they could be in the location table and not in the results table.

I am using the Agency table and Results table to compute my dynamic Top N and it is working great, but when i want put in the branch, it is throwing things off.

Here is a sample of what i have so far that is working with the dynamic Top 5

Agency Code     Agency Name      Amount

11                          Agency D            2000

2                            Agency A            1900

5                            Agency B             800

10                          Agency E             600

9                            Agency F             559

Here is what i would like to do

Agency Code     Agency Name        Location     Amount

11                          Agency D          Location 1       1000

11                          Agency D          Location 3       600

11                          Agency D          Location 2       400

2                            Agency A          Location 1       1900

5                            Agency B          Location 1       800

10                          Agency E          Location 1       400

10                          Agency E          Location 2       200

9                            Agency F          Location 1       559

But what it looks to be doing is ignoring my top 5 slicer because it is listing Main Agencies that are not in the top 5 in terms of amounts collected.

I have the relationships created between all of the tables, but am not sure how to make my dynamic TopN affect each one.

Here are my current measures in Results for the Top N ranking.

TotalAmount = SUMX(Results,Results[Amount])

Total Amount = IF(HASONEVALUE('Top'[Top]),IF(RANKX(ALLSELECTED(Main_Agency),[TotalAmount]) <= VALUES('Top'[Top]),[TotalAmount],BLANK()),[TotalAmount])

How can i make that Total Amount measure impact all of the tables that are linked to the Results table?

[Sean]

@amotto11

Here are the STEPS... (you can rename all/any of these)

1) in the Results table create a COLUMN

Main Producer Related = RELATED(AgencyCode[Main Producer])

2) then modify your TotalAmount MEASURE

TotalAmount =
CALCULATE (
    SUM ( Results[Amount] ),
    ALLEXCEPT ( Results, Results[Main Producer Related] )
)

3) then modify your Total Amount MEASURE

Total Amount =
IF (
    HASONEVALUE ( 'Top'[Top] ),
    IF (
        RANKX ( ALLSELECTED ( Results[Main Producer Related] ), [TotalAmount] )
            <= VALUES ( 'Top'[Top] ),
        CALCULATE (
            SUM ( Results[Amount] ),
            FILTER ( Results, [Rank Measure] <= VALUES ( 'Top'[Top] ) )
        ),
        BLANK ()
    )
)

And here's the result

Hope this helps!

[amotto11]

This is doing exactly what i was looking for. Thank you for the help. I will try to do this on my dataset and let you know if there are any problems. In the meantime i will mark as solved.