topic Re: Average of DISTINCT count (two columns) in a filtered table in DAX Commands and Tips

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

sashaxiv — Tue, 14 Sep 2021 16:17:58 GMT

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

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

daxer-almighty — Tue, 14 Sep 2021 22:10:53 GMT

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

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

sashaxiv — Wed, 15 Sep 2021 07:20:11 GMT

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!

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

daxer-almighty — Wed, 15 Sep 2021 08:04:48 GMT

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.

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

sashaxiv — Wed, 15 Sep 2021 09:42:02 GMT

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

Thanks!

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

daxer-almighty — Wed, 15 Sep 2021 16:31:00 GMT

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