Measure to Create Static Top Item List

[astano05]

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. What I'm looking to do is create a measure that will give me the top 250 items by sales dollars for 2020 in a market channel. I then want that 250 item list to remain static as i view customer sales on those 250 items. Using the Top N filters will filter out the top 250 items for each customer, but i want to compare the same list of items for each customer.

The goal is to see what customers are/are not buying those top items. 

Example:

Customer 1

Item	Sales
Item 1	300
Item 2	200
...	...
Item 250	50

Customer 2

Item	Sales
Item 1	1000
Item 2	200
...	...
Item 250	10

[jdbuchanan71 · Accepted solution]

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 )

[astano05]

This did the trick! The measure seems to be working now! I adjusted back to the 250 and it looks good. 

Thank you for your help

[jdbuchanan71]

That is strange, I can't see anything wrong with your sytnax.  Try it like this, moving the + IF to the end makes it easier to turn off for testing.

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 ( VALUES ( 'Item'[Item Number External] ) IN ( _TopProducts ), 0 )

Any chance you can share your .pbix file (post it to one drive or drop box and share the link)?

[astano05]

I very much appreciate your help so far. Unfortunately I'm not at liberty to share the file, and it's connected to a large, live azure db. 

I moved the +IF to the end but i get the same error. The issue seems to be that it's expecting a single value where VALUES() is in the formula, but a table is supplied. I'm not sure of any alternative way to make it work though.

[jdbuchanan71]

If you change both steps to look at the same measure does the error go away?

[astano05]

No i get the same error either way. The measure works without the IF portion. I get the error when I add it in, but the functionality of seeing the total number of top products is important to the report.

[jdbuchanan71]

Right, forcing the 0 on the blanks but keeping the top 250, try 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
IF ( VALUES ( 'Product'[ProductKey] ) IN ( _TopProducts ), 0 ) +
    CALCULATE (
        [Sales Amount],
        FILTER ( VALUES ( 'Product'[ProductKey] ), 'Product'[ProductKey] IN ( _TopProducts ) )
     )

It works on my sample to put the 0 on the rows that are in the topn (I am looking at 10 here) even when I filter to a single customer that only bought a portion of the list.

[astano05]

This is exactly what i need. I'm not sure why i'm getting an error when adding in the if statement and you're not.

I get this error using this updated formula. Note that the format is identical, just with the proper names.

[jdbuchanan71]

Copy your measure and share it here.

[astano05]

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
if(VALUES('Item'[Item Number External]) IN (_TopProducts),0)+
    CALCULATE (
        [Net Sales Product YTD],
        FILTER ( VALUES ( 'Item'[Item Number External] ), 'Item'[Item Number External] IN ( _TopProducts ) )
     )

Note: i changed the 250 to 10 for now and the Top Products variable uses Last Year sales, while the calculate function in the return looks at YTD

[jdbuchanan71]

@astano05 

Try 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.

[astano05]

This 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.

[amitchandak]

@astano05 , Try measure like

Measure =
Var _tab = TOPN(250,all(Table[Item]),[Sales],DESC)
return
calculate([sales], filter(Table, Table[item] in _tab))

[astano05]

Thanks for the response. I get an error with that measure that the visual can't be displayed.

I used this measure which is getting me close:

rankx(CALCULATETABLE(all('Item'),'Market Channel'[Market Channel]="ED"),[Net Sales LY])

However, the rankings still change when i change the customer using a slicer. It ranks the items in the context of the customer.