cancel
Showing results for
Did you mean:
Regular Visitor

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

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?

Regards

1 ACCEPTED SOLUTION
Solution Sage
``````// 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.``````
5 REPLIES 5
Regular Visitor

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!

Solution Sage

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.

Regular Visitor

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

Thanks!

Solution Sage

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

Solution Sage
``````// 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.``````

Announcements