Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sashaxiv
Frequent 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
Frequent 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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors