Calculate customer count when they have bought more than 3 products per week.

[tondeli]

I have sales data that includes columns:

• date
• week
• customer_id
• receipt_id
• product_id
• sales_pcs
• sales_eur

I'd like to create a measure that calculates customer count (distinct) when customer have bought more than 3 products in a week. Also I'd like to visualize this measure with Card.

products per week = distinctcount(sales[product_id])/distinctcount(sales[week])

I created a table visualization that contains week, customer_id and products per week. I also can add filter to my visualization and filter products per week >=3. I can see from this table how many customer_ids match my filter. When I try to transform my visualization to Card I'll get total amount of customer_ids. Filter do not work.

I tried to create another measure.

customer count = calculate(distinctcount(sales[customer_id]);filter(sales;[products per week]>=3)) 

This one doesn't give any answer.

I think that my problem has something to do with the order of the calculations. Is there a way to manipulate this? Also is there anything wrong with my second measure and why it doesn't give any aswer?

I'll still try to google my problem and try to find aswer from this site aswell. But any advices will be most appreciated.

[tondeli · Accepted solution]

Answering to my own question...

I created a new column just to calculate how many products each customer has bought.

Products purchased = calculate(distinctcount(sales[product_id]);allexcept(sales;sales[customer_id]))/distinctcount(sales[week])

Customer count = calculate(distinctcount(sales[customer_id]);filter(sales;[Products purchased]>=3))

Everything works now!