Count sales made by others in my sales area

[pazdzierz]

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!

[v-xianjtan-msft]

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.

[pazdzierz]

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. 

[v-xianjtan-msft · Accepted solution]

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.

[pazdzierz]

Many thanks Jarvis! Works perfect 😎 

[Irwan]

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

[pazdzierz]

Its a data.

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

Thanks!