Group customers by counting of their purchase and show in matrix table

[blader1989]

Hi guys,

I'm now building a DAX that will show us the total count of customer by the number of purchased product with total sales is large than 0 in the matrix table when we filter year & month as my below picture.-

I have tried all the dax on google but can't find the good solution so I hope you can help me. Here is the link of my files. Thank you in advance!

[tharunkumarRTK]

@blader1989 

I think there are many ways to achieve this result, one approach that I could suggest is:
create a list of numbers from 1 to n (distinct count of products) and created a measure to find number of customers:

DistinctCountOfCustomers = 
Var __productCount = MAX(Query1[NoOfProducts])
Var __Customers = ADDCOLUMNS(VALUES(raw[Customer Code]), "ProductCount",  CALCULATE( COUNTROWS(VALUES(raw[Product Name])))) 
var Result = FILTER(__Customers, [ProductCount] = __productCount) 
RETURN COUNTROWS(Result) 

If you want you can filter out customers whose total sale value is less than zero.

I am attaching the file here: 

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[blader1989]

Hi @tharunkumarRTK , thank you for your solution. It's look promising so how can I add the condition that the total sales is large than 0 in your formula? In my power BI file, I already have the totalsales DAX.

[tharunkumarRTK · Accepted solution]

@blader1989 
Assuming your sales measure name is [SalesAmount]

DistinctCountOfCustomers = 
Var __productCount = MAX(Query1[NoOfProducts])
Var __Customers = ADDCOLUMNS(Filter(VALUES(raw[Customer Code]), [SalesAmount] >0 ), "ProductCount",  CALCULATE( COUNTROWS(VALUES(raw[Product Name])))) 
var Result = FILTER(__Customers, [ProductCount] = __productCount) 
RETURN COUNTROWS(Result) 

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[blader1989]

Hi @tharunkumarRTK , thank you so much. I have added your approach and it's look very good now we are facing new challegening and I hope you can help us that when we click the number of SKUs purchase group on the matrix table, it will only show us which product and customer code in the 2 tables below?