Forum Discussion
astano05
Helper III
Helper IIIMeasure to Create Static Top Item List
I'm looking to create a measure that will allow me to filter out only the top 250 items. I'm connected to a live database, so I cannot create columns. I have an item, customer, and sales table. ...
Strange, perhaps try it like this.
Top 250 Item Sales = VAR _TopN = 10 VAR _TopProducts = CALCULATETABLE ( TOPN ( _TopN, ALL ( 'Item'[Item Number External] ), [Net Sales Product LY], DESC ), 'Market Channel'[Market Channel] = "ED", REMOVEFILTERS ( Customer ), ALLEXCEPT ( 'Item', 'Item'[Item Number External] ) ) RETURN CALCULATE ( [Net Sales Product YTD], FILTER ( VALUES ( 'Item'[Item Number External] ), 'Item'[Item Number External] IN ( _TopProducts ) ) ) + IF ( SELECTEDVALUE ( 'Item'[Item Number External] ) IN ( _TopProducts ), 0 )
jdbuchanan71
Super User
Super UserTry something like this.
Top 250 Products Sales =
VAR _TopN = 250
VAR _TopProducts =
CALCULATETABLE (
TOPN ( _TopN, ALL ( 'Product'[ProductKey] ), [Sales Amount], DESC ),
REMOVEFILTERS ( Customer ),
ALLEXCEPT ( 'Product', 'Product'[ProductKey] )
)
RETURN
CALCULATE (
[Sales Amount],
FILTER ( VALUES ( 'Product'[ProductKey] ), 'Product'[ProductKey] IN ( _TopProducts ) )
)Without knowing the structure of your model it is difficult to give a more precise example. This measure works against the Contoso sample database.
astano05Helper III
Helper IIIThis works much closer. The problem is that it still shows all products, just with blanks for those outside the top 250. I cant just filter out the blanks becasue when i look at customers, I still want to see all the 250 products, even if there are no sales.