Distinct Count SKUs when Sales >$X

[marksaba]

I'm looking to create a formula that allows me to count the number of Items where Sales are greater than $X. I tried the below formula but it is not working properly as it looks to the lowest level of data, which is pulled at the Item, Week, and Retailer Level. I would like this to pull back Total Sales for a SKU during the selected Time Period.

SKU Count Last Week >$10000 = CALCULATE(DISTINCTCOUNT('Store Inventory and Sales Last Week'[NWL Material ID]),FILTER('Store Inventory and Sales Last Week','Store Inventory and Sales Last Week'[POS Sales Last 01 Wk]>10000))

As can be seen below, this formula does not recognize the highlighted Materials as >$10,000 because there is not one retailer over one week that has sales >$10,000. How can I have this look at Total Sales for the Item at the level I have cut down to?  

[v-rzhou-msft · Accepted solution]

Hi @marksaba ,

Please try code as below to create a measure.

SKU Count Last Week >$10000 =
VAR _SUMMARIZE =
    SUMMARIZE (
        'Store Inventory and Sales Last Week',
        'Store Inventory and Sales Last Week'[NWL Material ID],
        "Sum",
            CALCULATE (
                SUM ( 'Store Inventory and Sales Last Week'[POS Sales Last 01 Wk] )
            )
    )
RETURN
    COUNTROWS ( FILTER ( _SUMMARIZE, [Sum] > 10000 ) )

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marksaba]

Sorry for not checking the forum earlier but this works perfectly! You're a gem, thank you so much!

[ray_aramburo]

First create a measure like this :

Total Sales = CALCULATE(SUM(SalesColumn),ALLEXCEPT(MaterialsTable, MaterialsTable[ItemColumn]))

Then you can create a new measure like this:

Items Count = COUNTROWS(FILTER(VALUES(MaterialsTable[ItemColumn]), [Total Sales] > 10000))