DAX / PowerBI: Customers with only sales in a selected productgroup
Multi tool use
up vote
0
down vote
favorite
I want to get a table which list all customers with qty sold, which do not have sales in a not selected product group.
Sample Data
When I select PGC, I only want to see Customer C & F, which have no sales for the product groups PGA & PGB.
When Selecting PGA, I only want to See Customer G.
When Selecting PGB, I expect no results.
The datamodel used: 
I have now the formula, that does quite opposite.
Unique Qty Sold = CALCULATE([Qty Sold];
FILTER(
SUMMARIZE(Sales; Customer[CustomerID]; "ProductsBought"; DISTINCTCOUNT(Products[ProductGroup]));
[ProductsBought]=COUNTROWS(VALUES(Products[ProductGroup]))
)
)
When selecting the productgroups, It finds all customers who have bought both products.
Anyone knows what formula can be used?
The sample file can be found at: downloadlink
powerbi dax powerpivot
asked Nov 8 at 12:50
WimV
890210
add a comment |
up vote
0
down vote
favorite
I want to get a table which list all customers with qty sold, which do not have sales in a not selected product group.
Sample Data
When I select PGC, I only want to see Customer C & F, which have no sales for the product groups PGA & PGB.
When Selecting PGA, I only want to See Customer G.
When Selecting PGB, I expect no results.
The datamodel used:
I have now the formula, that does quite opposite.
Unique Qty Sold = CALCULATE([Qty Sold];
FILTER(
SUMMARIZE(Sales; Customer[CustomerID]; "ProductsBought"; DISTINCTCOUNT(Products[ProductGroup]));
[ProductsBought]=COUNTROWS(VALUES(Products[ProductGroup]))
)
)
When selecting the productgroups, It finds all customers who have bought both products.
Anyone knows what formula can be used?
The sample file can be found at: downloadlink
powerbi dax powerpivot
asked Nov 8 at 12:50
WimV
890210
Your link is broken
– StelioK
Nov 9 at 2:24
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I want to get a table which list all customers with qty sold, which do not have sales in a not selected product group.
Sample Data
When I select PGC, I only want to see Customer C & F, which have no sales for the product groups PGA & PGB.
When Selecting PGA, I only want to See Customer G.
When Selecting PGB, I expect no results.
The datamodel used:
I have now the formula, that does quite opposite.
Unique Qty Sold = CALCULATE([Qty Sold];
FILTER(
SUMMARIZE(Sales; Customer[CustomerID]; "ProductsBought"; DISTINCTCOUNT(Products[ProductGroup]));
[ProductsBought]=COUNTROWS(VALUES(Products[ProductGroup]))
)
)
When selecting the productgroups, It finds all customers who have bought both products.
Anyone knows what formula can be used?
The sample file can be found at: downloadlink
powerbi dax powerpivot
asked Nov 8 at 12:50
WimV
890210
I want to get a table which list all customers with qty sold, which do not have sales in a not selected product group.
Sample Data
When I select PGC, I only want to see Customer C & F, which have no sales for the product groups PGA & PGB.
When Selecting PGA, I only want to See Customer G.
When Selecting PGB, I expect no results.
The datamodel used:
I have now the formula, that does quite opposite.
Unique Qty Sold = CALCULATE([Qty Sold];
FILTER(
SUMMARIZE(Sales; Customer[CustomerID]; "ProductsBought"; DISTINCTCOUNT(Products[ProductGroup]));
[ProductsBought]=COUNTROWS(VALUES(Products[ProductGroup]))
)
)
When selecting the productgroups, It finds all customers who have bought both products.
Anyone knows what formula can be used?
The sample file can be found at: downloadlink
powerbi dax powerpivot
powerbi dax powerpivot
asked Nov 8 at 12:50
WimV
890210
asked Nov 8 at 12:50
WimV
890210
asked Nov 8 at 12:50
WimV
890210
asked Nov 8 at 12:50
WimV
890210
asked Nov 8 at 12:50
WimV
890210
890210
Your link is broken
– StelioK
Nov 9 at 2:24
add a comment |
Your link is broken
– StelioK
Nov 9 at 2:24
Your link is broken
– StelioK
Nov 9 at 2:24
Your link is broken
– StelioK
Nov 9 at 2:24
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53208121%2fdax-powerbi-customers-with-only-sales-in-a-selected-productgroup%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
mu9HON0Wqqzi
Your link is broken
– StelioK
Nov 9 at 2:24