cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sashaxiv
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?

Thanks in advance!

Regards

1 ACCEPTED SOLUTION
daxer-almighty
Solution Sage
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.

View solution in original post

5 REPLIES 5
sashaxiv
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!

@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?

daxeralmighty_0-1631693048325.png

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

 

 

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

Thanks!

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

daxer-almighty
Solution Sage
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.

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors