Forum Discussion

astano05's avatar
astano05
Helper 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. ...
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 )
jdbuchanan71's avatar
jdbuchanan71
Super 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
    Helper 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
      Super User
      5 years ago

      Copy your measure and share it here.

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