DAX / PowerBI: Customers with only sales in a selected productgroup
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Your link is broken
– StelioK
Nov 9 at 2:24