topic Re: Count sales made by others in my sales area in DAX Commands and Tips

Count sales made by others in my sales area

pazdzierz — Wed, 04 Dec 2024 22:43:20 GMT

Hi,

Stucked with something that seems pretty simple... struggled with GPT with no result

I'd like to count how many products has been sold by other dealers in my zone assuming that:

there are zones with more than one dealers - for example for dealer B i want to show value 7.
there are dealers appearing in more than one zone - for example for dealer D i want to show value 3.

productID	dealer	zone	dealer zone?
1	A	1	1
2	A	1	1
3	B	1	1
4	B	2	0
5	C	1	0
6	D	2	1
7	D	2	1
8	C	2	1
9	D	3	1
10	D	3	1
11	D	1	0
12	D	1	0
13	D	1	0
14	D	1	0
15	B	3	0

I was pretty close with below measure, but it wont work with dealers which appear in more than one zone:

if(SALES[dealer zone?]=1, (SUMX( FILTER( SALES, SALES[zone] = EARLIER(SALES[zone]) && SALES[dealer] <> EARLIER(SALES[dealer]) ), 1 )),0)

cheers!

Re: Count sales made by others in my sales area

Irwan — Thu, 05 Dec 2024 00:29:34 GMT

hello pazdzierz

how to get 7 for dealer B and 3 for dealer D?

is column 'dealer zone?' a data or a calculation?

Thank you

Re: Count sales made by others in my sales area

Anonymous — Thu, 05 Dec 2024 06:12:34 GMT

Hi pazdzierz

Why should the calculation for distributor D equal 3?
If I understand you correctly, you want to calculate the product sales counts for each dealer other than themselves in each zone. So according to your example data, in zone 1, the count of products sales by dealers other than dealer D should be 4. Please correct me if I've misunderstood.

You can create a measure with the following DAX:

OtherDealersSales = VAR CurrentZone = SELECTEDVALUE('SALES'[zone]) VAR CurrentDealer = SELECTEDVALUE('SALES'[dealer]) RETURN CALCULATE( COUNTROWS('SALES'), 'SALES'[zone] = CurrentZone, 'SALES'[dealer] <> CurrentDealer )

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count sales made by others in my sales area

pazdzierz — Thu, 05 Dec 2024 06:43:13 GMT

Thank you for your reply. I'd like to see 3 because dealer D belongs to zone 2 and 3. So total number of "sales by other" for dealer D is 3 (product ID 15, 8 and 4). Info regarding if dealer belongs to particular zone is in column 4.

Re: Count sales made by others in my sales area

pazdzierz — Thu, 05 Dec 2024 06:49:09 GMT

Its a data.

Dealer B belongs to zone 1 - total number of sales made by others in zone 1 is 7.

Thanks!

Re: Count sales made by others in my sales area

Anonymous — Thu, 05 Dec 2024 08:52:25 GMT

Hi pazdzierz

Thanks for the explanation, please try using the following measure:

OtherDealersSales = VAR CurrentDealer = SELECTEDVALUE(SALES[dealer]) VAR DealerZones = CALCULATETABLE( VALUES(SALES[zone]), SALES[dealer zone?] = 1, SALES[dealer] = CurrentDealer ) VAR SalesExcludingSelf = CALCULATETABLE( SALES, SALES[zone] IN DealerZones, SALES[dealer] <> CurrentDealer ) RETURN COUNTROWS(SalesExcludingSelf)

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count sales made by others in my sales area

pazdzierz — Thu, 05 Dec 2024 09:06:31 GMT

Many thanks Jarvis! Works perfect 😎