cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
LinhDO Frequent Visitor
Frequent Visitor

Count distinct customers having sales

Hi everyone,

 I m new to Power BI and struggling with Power BI DAX.

I'm trying to count Distinct customers having sales. It means when selecting relevant months, only customers having sales will be counted. 

I used the following measure:

"CALCULATE(DISTINCTCOUNT(Rawdata[cust code]),
FILTER(Rawdata, CALCULATE(SUM(Rawdata[f_Value]),
ALLEXCEPT(Rawdata,Rawdata[cust code]))>0))"
Error: It does not work with the condition "having sales > 0". For example, I select Oct 2019, it counts all distinct customers, including customers having sales < 0. I think the Filter function does not work, it just summerize the sales of customers of any time, not based on the months that I selected (ex Oct'19).
 
Hope that you can help me on this issue.
Thanks a lot in advance!

 

2 REPLIES 2
vimal_parmar Senior Member
Senior Member

Re: Count distinct customers having sales

@LinhDO Please use this measure. In case this is not what you expected, share some dummy data along with expected result

 Measure = CALCULATE(DISTINCTCOUNT('Table'[cust code]),FILTER('Table','Table'[f_value]>0))

 

LinhDO Frequent Visitor
Frequent Visitor

Re: Count distinct customers having sales

Hi, 

Your syntax does not work as i expect.

You can see the raw data on this link:

https://drive.google.com/file/d/1tneUjqd7rpk9dHkcjgodw2UNtQb41Roi/view?usp=sharing 

In sheet "Raw", total customers in a month, 1 customer can purchase more than 1 time. When calculating active customers in a month, I use pivot to summarize sales by cust code then count 1 if sales by cust code in a month > 0.

 

Hope its clear. 🙂

thx a lot!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)