Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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:
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!
Solved! Go to 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.
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.
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.
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.
Many thanks Jarvis! Works perfect 😎
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!
User | Count |
---|---|
21 | |
20 | |
12 | |
10 | |
8 |
User | Count |
---|---|
30 | |
28 | |
14 | |
13 | |
11 |