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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
pazdzierz
Frequent Visitor

Count sales made by others in my sales area

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. 
productIDdealerzonedealer zone?
1A11
2A11
3B11
4B20
5C10
6D21
7D21
8C21
9D31
10D31
11D10
12D10
13D10
14D10
15B30

 

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!

1 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)

 

vxianjtanmsft_0-1733388725038.png

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.

 

View solution in original post

6 REPLIES 6
v-xianjtan-msft
Community Support
Community Support

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
)

vxianjtanmsft_0-1733378619912.png

 

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.

 

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. 

pazdzierz_0-1733380727397.png

 

 

 

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)

 

vxianjtanmsft_0-1733388725038.png

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.

 

Many thanks Jarvis! Works perfect 😎 

Irwan
Super User
Super User

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

Its a data.

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

Thanks!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors