Average of DISTINCT count (two columns) in a filtered table

[sashaxiv]

Morning All!

I would like to ask for some tips regarding next problem.

I have the following tables in my data model:

• Product Category
• Stock (sku, id_store, units, date)
• Stores(id_Store)

The visualizacion I am looking for is  Category, average number of diferent skus by store in a given date (today-2)

For instance:

• in product category "Toys" there are 30 diferent skus in average by store 
• in product category "Tools" there are 40 diferent skus in average by store 

Any recommendations in order to get this visualization with a Dax measure?

Thanks in advance!

Regards

[sashaxiv]

Thank you very much! This was exactly what I was looking for.

Now I need to filter stock table to be able to count skus in a given date (today-2), not in all the stock table.

Thanks again!

[daxer-almighty]

@sashaxiv 

Of course, the measure I gave you reacts to any slicing. That was the idea.

By the way, would you please mark my answer as THE answer and give me a thumbs-up?

The former is (almost) required as it'll help others, the latter is optional (as it'll help only me). Thanks.

[sashaxiv]

By the way, where would you introduce a DAX filter sentence to get the stock filtered by date?

Thanks!

[daxer-almighty]

Don't know what you really mean... If you start slicing by dates, the measure will adjust itself automatically.

[daxer-almighty · Accepted solution]

// Not really clear what you want
// but here's an attempt to understand...

[Avg #SKUs (by Store)] =
AVERAGEX(
    // Averages the number of all distinct
    // SKU's in the current context over all
    // visible stores.
    SUMMARIZE(
        Stock,
        Stores[StoreID]
    ),
    CALCULATE(
        DISTINCTCOUNT( Stock[sku] )
    )
)

// If you slice your data by categories
// it'll return the average number of
// SKU's in the given category/categories.
// Again, the averaging is done over
// all visible stores.