Forum Discussion
Filter and Count
Hello,
We currently recording product sales and every transaction is one row/customer.We would like to filter on number of people who have spent more than 100,000
However if someone has spent 300,000 it shiuld count it as thrice while the row will be just one customer
Can you suggest how can we do this in power BI?
In tableau we used to put the unique row with condition of sales >100000 and would like to do the same in Power BI
Hi, sanjaymanghnani ;
You could create a measure as follows:
Measure = VAR _amount = CALCULATE (SUM ( [Amount] ),ALLEXCEPT ( 'Table', 'Table'[Category], 'Table'[Month] ) ) VAR _count = IF ( _amount >= 100000, DIVIDE ( _amount, 100000 ) ) RETURN IF ( HASONEVALUE ( 'Table'[Category] ), _count, CALCULATE (COUNT ( 'Table'[Category] ),FILTER ( ALLEXCEPT ( 'Table', 'Table'[Month] ), [Amount] >= 100000 )))The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi sanjaymanghnani ,
I think it is better to provide a sample data that we can work with to help you with your desired output.
Thank you
Name Amount Month Category A 100 1 Watches B 100,000 2 Cosmetics C 20,000 3 Perfumes D 300,000 1 Electronics E 400,000 2 Electronics F 10,000 4 Gold Output Month 1 2 3 4 5 Cosmetics 1 Electronics 3 4 Total Customers 1 2 so they are 3 customers who have exceeded the purchase of 100,000 in first and second month
in January only D has purchased 300,000 so the count is 3 but its just one customer so total customers 1
in Feburary there are two customers of 400,000 and 100,000 hence count 5
Hi, sanjaymanghnani ;
You could create a measure as follows:
Measure = VAR _amount = CALCULATE (SUM ( [Amount] ),ALLEXCEPT ( 'Table', 'Table'[Category], 'Table'[Month] ) ) VAR _count = IF ( _amount >= 100000, DIVIDE ( _amount, 100000 ) ) RETURN IF ( HASONEVALUE ( 'Table'[Category] ), _count, CALCULATE (COUNT ( 'Table'[Category] ),FILTER ( ALLEXCEPT ( 'Table', 'Table'[Month] ), [Amount] >= 100000 )))The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
