Calculating number of shops that sold at least N quantity of a single item

DonTwan — Mon, 04 Sep 2023 16:39:04 GMT

I have a sale table that looks like this:

BookingDate	ShopId	ArticleId	QtyPieces
10/05/2023	704788	31452	6
10/05/2023	704788	30891	1
10/05/2023	704788	30544	10

And would like to know how many shops have sold at least 4 total quantity for each of the articles

My matrix is setup as following

the values are:

Distinct count of shops, sum of QtyPieces and Distinct count of shops where at least 4 of the article is sold.

CALCULATE( DISTINCTCOUNT(F_Sales_22May23[ClientId]), FILTER( F_Sales_22May23, SUM(F_Sales_22May23[QtyPieces])> 4 ) )

the result is

because the formulla evaluates for each filter context individually, the number of distinct shops = number of shops with at least 4 in quantity sales.

How do i rewrite the formulla so the totals only count shops that have less than 4 Qty sales.

Re: Calculating number of shops that sold at least N quantity of a single item

lbendlin — Tue, 05 Sep 2023 19:46:24 GMT

Instead of

FILTER( F_Sales_22May23, SUM(F_Sales_22May23[QtyPieces])> 4 )

use

FILTER( F_Sales_22May23, [QtyPieces]> 4 )

F_Sales_22May23[QtyPieces]> 4

Same with the <4 approach.

topic Re: Calculating number of shops that sold at least N quantity of a single item in DAX Commands and Tips

Calculating number of shops that sold at least N quantity of a single item

Re: Calculating number of shops that sold at least N quantity of a single item