Home Forums Power Pivot TopN article?

Tagged: 

This topic contains 3 replies, has 2 voices, and was last updated by  tomallan 4 years, 1 month ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #4944

    Mark Walter
    Participant
    • Started: 9
    • Replies: 19
    • Total: 28

    hi Tom,

    regarding the post on “Displaying topn, bottom n..”

    and this formula:

    Calc([total sales],FILTER(Customers,RANKX(ALL(Customers),[Total Sales])<1000))

    how can i rank by a specific category column in the customer table? must i only filter the entire table?  we specify a rank table and column and get an error.

    thanks!

     

     

    #4953

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    Mark,

    In the example code, ALL() is used to clear the filter context coming from the pivot (otherwise results would rank the current row by itself).

    Would like to see how you have implemented a rank table in your model. Can you put together some sample anonymized data in a workbook and attach?

    Tom

    #4956

    Mark Walter
    Participant
    • Started: 9
    • Replies: 19
    • Total: 28

    Hi Tom,

    Sorry, my question could have been clearer… See attached workbook.. Our customer table connects to our sales file based on Cust#, but our customers may have a few diff Cust#s.

    When we go to use this rankx (topn) style formula, I want to tell the formula to rank customers based on ‘group name’ rather than over all the individual customer numbers…

    Our work around is to related group name to our sales file from the full customer table… then bring in a second trimmed version of the customer table that only lists the unique customer group names… then point the rankx formula to rank off of just the group name table…

    Unless there is another way to do this in the measure..

    Thanks for looking!

    Attachments:
    You must be logged in to view attached files.
    #4976

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    Mark,

    Sorry I took awhile getting back to you, the answer is quite easy and it is a case of “Power Query to the Rescue”, with the help of a DAX/Power Pivot feature called context transition.

    I used Power Query to create a parent table to customers called “Customer Groups”, which consists of customer group names with duplicates removed.

    After relating Customer Groups to the Customers table, I then used the context transition feature. The context transition feature, which sounds like a mouthful, is actually not that hard to grasp and work with: when you create a calculated column in a parent table (or grand-parent table, as in this case) and the formula for that column is wrapped within a CALCULATE, the Power Pivot engine will — for each row — convert the values of the other columns in the same row into filters.

    So to try to stop my long-winded explanation, creating the calculated column

    Group Sales = CALCULATE ( SUM ( Sales[Sales] ) )

    is transitioned by the formula engine into:

    = CALCULATE ( SUM ( Sales[Sales] ), FILTER ( ‘Customer Groups’, [Group Name] = [what ever value is in the current row of the Customer Groups table for Group Name] ) )

    and that formula, through the power of relationships, produces the total sales for each customer group. Now you can do your ranking in the customer groups table with a simple measure following the typical pattern:

    Group Name Rank:= RANKX ( ALL ( ‘Customer Groups’ ), [Total Group Sales] )

    In the workbook I wrap an IF ( HASONEVALUE ( ‘Customer Groups’ ) ) around the ranking function only to suppress ranking the grand total.

    Tom

    Attachments:
    You must be logged in to view attached files.
Viewing 4 posts - 1 through 4 (of 4 total)

The forum ‘Power Pivot’ is closed to new topics and replies.