TopN article?

[Mark WalterParticipant Started: 9Replies: 19Total: 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!

 

 

[tomallanKeymaster Started: 0Replies: 417Total: 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

[Mark WalterParticipant Started: 9Replies: 19Total: 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.

[tomallanKeymaster Started: 0Replies: 417Total: 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.

[Author]

Posts