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