Count of customers with sales greater than "n" in a month for a product

[Anonymous]

Helo all,

I've built a report in which I have shown a matrix of poducts and customers.The values show the count of sales in a month.

For eg product "A:' has been sold by customer "X" 5 times in a month.

Now I want to create a filter for sales>4,>5,>6 etc in a month

The report has to been shown product wise.

For eg product A has been sold greater than 5 times by 10 customers. So I want the product in rows and the count of customers in front of them and this should be filtered according to the filter created above(sales>4,>5>6,

Please help

[Washivale]

Hi @Anonymous,

your requirement from the question, says "For eg product A has been sold greater than 5 times by 10 customers. So I want the product in rows and the count of customers in front of them and this should be filtered according to the filter created above(sales>4,>5>6,"

by this logic, if you add product to rows, you should get desired results, however, if you add customer to rows, then your count will always return 1. let me know if i missed anything from your initial requirements.

[Anonymous]

@Washivale,

It's still not working.

The system is not understanding the count of sales in a month.

And till that happens i cannot calculate the count of customers either as a value in a measure or product wise.

[Anonymous]

@Washivale,

I cannot do that because of the highly sensitive nature of the data.

Nevertheless, it is a sales table with data coming daily at customer and product level.

The measure i have used to calculate the sales in a month: (I have given a month filter for user selection)

SalesCount=distinctcount(SalesTable[date])

Now, i have used this to calculate a measure that counts customers with sales equal to 5,10,15 in a month:

CountCustomer=calculate(distinctcount(SalesTable[Customer]),filter(SalesTable,[SalesCount] = 5))

Shouldn't the above measure give me the count of customers as a single value with sales greater than 5??

 PS: The month filter is coming from a date table that is linked to the sales table

[Washivale]

Hi @Anonymous,

I have created a calculated table from Sales Table as below:

SummarizedSales = SUMMARIZECOLUMNS(Sales[Customer Name],Sales[Product Name],Sales[Date].[Month],Sales[Date].[Year],"Sales Count", COUNTA(Sales[Customer Name]))

then added a custom column:

Group = IF(SummarizedSales[Sales Count]>15, ">15", IF(SummarizedSales[Sales Count]>10,">10", if(SummarizedSales[Sales Count]>5,">5","<5")))

let me know if it works 

[Anonymous]

@Washivale,

Hey I really appreciate your help, but I seem to be getting confused.

Can we start from the beggining,

Is it possible to get the count of customers with sales equal to 4(count of days=4) ,on the selection of a month.

My intent is to show the no of customers who have been billed 4 times in a month, 3 times in a month, etc. Hence i need the count of customers in a measure.

Why am I failing to count customers based on the filter applied on the count of days?

[edhans]

@Anonymous,

It is really difficult to come up with an answer when you won't share what the model really looks like. Just load it with fake data. As far as I can tell, the solution I provided answers the original question with the original data, but obviously you have a different model, and are applying different filters, so of course my solution won't work as it takes nothing else into account.

You could put sample data in an Excel spreadsheet in tables, and then we could see your model in Power Pivot and solve it there if necessary, then you could take the DAX to your actual Power BI model.

[Anonymous · Accepted solution]

I have been able to solve it.

I just needed a distributor master table in my model.

@Washivale, @edhansThank you for your help 🙂

[edhans]

Glad you were able to get it working @Anonymous

[Washivale]

Hey @Anonymous,

Please share a sample snapshot from your raw table or pbix file. it would be easier to find solution with sample data.

Regards,

Washivale

[Washivale]

Hi Payal,

Assuming you have your raw table in following format

Customer Name|Product Name|Billed Date|... and so one

along with Date table which will have relationship with your raw table on date to get monthwise data.

Create a Measure for billed count

Billed Count=Calculate(Counta([Customer Name])

measure 2 that will help you to count customers billed greater than 5

Customers > 5 =Calculate(Counta([Customer Name]), [Billed Count] > 5)

use month as either slicer or columns on your matrix

add Products to Rows, and Customer >5 as measure to your data.

let me know if it works.

Thank you,
Sandeep

[Anonymous]

Hey @Washivale,

It did not work.

The result looks like:

Customers	Sales >5
Customer A	1
Customer B	1
Customer C	1

I want the count of customers with sales greater than 5 in a measure.

But I'am getting the result like above . Why is the count not taking place. For eg: here it should be 3

[Washivale]

HI Payal,

please add Product in the rows instead of customers. let me know output

Thanks,

Sandeep

[Anonymous]

@Washivale

I need the count of customers with sales greater than 5 for a month selection.

The products will come later.

[v-yuta-msft]

Hi payal4,

"For eg product A has been sold greater than 5 times by 10 customers. So I want the product in rows and the count of customers in front of them and this should be filtered according to the filter created above(sales>4,>5>6,"

<--- Could you share some sample data and clarify more details about your expected result of your example?

Regards,

Jimmy Tao

[Anonymous]

Hi @v-yuta-msft,

Following table shows the sales for a month :

Customers/Products	Product a	Product b	Product c	Product d
Customer A	10	8	5	7
Customer B	3	9	10	4
Customer C	6	3	8	5
Customer D	5	7	2	3

For eg: Customer "A"  was billed for product "a" 10 times in a month.

The above table can be shown in a matrix in power BI.

Now, the issue that I'am facing is to show the following:

Products	Count of Customers
Product a	2
Product b	3
Product c	2
Product d	1

Here, I want to count the no of customers for all products with sales greather than 5 times in a month.

For eg : product a has been sold to 2 customers(A,C) more than 5 times in a month

How do I count customers based on the matrix built above in the 1st table?

Also, I have to do the same for sales greater than 10 times, 15 times, etc.

The logic should be as mentioned but the format can be adjusted, so please feel free to suggest the different ways of showing the same.

[edhans]

You need to unpivot the data in your first sample in Power Query so it looks like this:

Then use this measure:

Number of Sales Above 5 = 
CALCULATE(
    COUNT(Sales[Customer]),
    Sales[Sales] > 5
    )

My table looks like this, which is what I think you want:

See this file.

[Anonymous]

Hi @edhans,

My transaction table is not like the matrix i created above.

It is just a sample to explain the concept.

[Anonymous]

Hi @v-yuta-msft,

Did you find a solution ?