Forum Discussion

amotto11's avatar
amotto11
Helper II
9 years ago
Solved

Show Top N values in Table

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 ...
Need Help
  • Sean's avatar
    Sean
    9 years ago

    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! Smiley Happy

amotto11's avatar
amotto11
Helper II
9 years ago

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's avatar
Sean
Community Champion
9 years ago

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 :smileyhappy:

Hope this helps! :smileyhappy: