Average number of item sold per store

[Kr1v3L1337]

Hi!

I want to calculate the average number of SKUs (items) sold per store. 

In my data hundreds of stores with thousands of products with sales.

I have success with calculating the number using this measure, but it is - very - slow.

Is there anyway to speed up this measure or structure it in another manner?

AVG SKUs Sold per Store = 
var _tab = SUMMARIZE(Sales,
                    Sales[Store Number],
                    "SKU",
                    CALCULATE(
distinctcount('Item'[Item Number]),
  FILTER('Item','Item'[Category] <> "Internal" && 'Item'[Category] <> "Unknown" && 
             'Item'[Subcategory] <> "Recycle" && [Qty] > 0)
)
)
return

AVERAGEX(_tab,[sku])

Thanks!

[OwenAuger · Accepted solution]

Hi @Kr1v3L1337 

I'm thinking a "related distinct count" style measure would work well here.

Here's one measure that might work, based on the pattern from the DAX Patterns page above.

I've assumed that [Qty] > 0 would only be true if Items actually appear in the Sales table.

AVG SKUs Sold per Store =
CALCULATE (
    AVERAGEX (
        VALUES ( Sales[Store Number] ),
        CALCULATE (
            VAR ItemsFromSales =
                SUMMARIZE ( Sales, 'Item'[Item Number] )
            RETURN
                SUMX ( ItemsFromSales, 1 )
        )
    ),
    KEEPFILTERS ( NOT 'Item'[Category] IN { "Internal", "Unknown" } ),
    KEEPFILTERS ( 'Item'[Subcategory] <> "Recycle" )
)

 Does this measure return correct results, and does it perform any better than your original measure?

Regards,

Owen