Forum Discussion
Solved
TOPN query
I created a table called SALES_MGR_NET_SALES to list the Sales Managers and their Sales with this DAX expression:
SALES_MGR_NET_SALES = SUMMARIZE(DIM_CUST_REP_HIER,DIM_CUST_REP_HIER[Sales Mgr],"Sales",sum(FACT_SLS[Net Sales]))
I want to use SALES_MGR_NET_SALES to create a Top 10 Sales Managers list. I created a second table TOP_TEN_SALES, which is suppposed to have the Top 10 Sales Managers, using this DAX expression:
TOP_TEN_SALES = TOPN(10,SUMMARIZE(SALES_MGR_NET_SALES,SALES_MGR_NET_SALES[Sales Mgr]),MAX(SALES_MGR_NET_SALES[Sales]))
However this results in the entire list of Sales Managers. How do I get it to limit it to the top ten?
- Hi there
The 3rd argument of TOPN is evaluated in row context of the table supplied in the 2nd argument.
Because of that, you need to wrap the 3rd argument in CALCULATE to turn the row context into a filter context, i.e. ensure the expression is evaluated in a filter context corresponding to each sales manager.
MAX or SUM would both work here and nice you already have distinct sales managers.
TOP_TEN_SALES =
TOPN (
10,
SUMMARIZE ( SALES_MGR_NET_SALES, SALES_MGR_NET_SALES[Sales Mgr] ),
CALCULATE ( MAX ( SALES_MGR_NET_SALES[Sales] ) )
)
2 Replies
- Hi there
The 3rd argument of TOPN is evaluated in row context of the table supplied in the 2nd argument.
Because of that, you need to wrap the 3rd argument in CALCULATE to turn the row context into a filter context, i.e. ensure the expression is evaluated in a filter context corresponding to each sales manager.
MAX or SUM would both work here and nice you already have distinct sales managers.
TOP_TEN_SALES =
TOPN (
10,
SUMMARIZE ( SALES_MGR_NET_SALES, SALES_MGR_NET_SALES[Sales Mgr] ),
CALCULATE ( MAX ( SALES_MGR_NET_SALES[Sales] ) )
)That worked.
Thanks!
