Forum Discussion

astano05's avatar
astano05
Icon for Helper III rankHelper III
5 years ago
Solved

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

ItemSales
Item 1300
Item 2200
......
Item 25050

 

Customer 2

ItemSales
Item 11000
Item 2200
......
Item 25010
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    5 years ago

    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 )

14 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      astano05
      Icon for Helper III rankHelper III
      5 years ago

      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's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    5 years ago

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

    • astano05's avatar
      astano05
      Icon for Helper III rankHelper III
      5 years ago

      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's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      astano05
      Icon for Helper III rankHelper III
      5 years ago

      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's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      astano05
      Icon for Helper III rankHelper III
      5 years ago

      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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    astano05 , Try measure like

     

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

    • astano05's avatar
      astano05
      Icon for Helper III rankHelper III
      5 years ago

      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.
  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      astano05
      Icon for Helper III rankHelper III
      5 years ago

      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.