topic Re: Count distinct customers having sales in DAX Commands and Tips

Count distinct customers having sales

LinhDO — Wed, 13 Nov 2019 14:03:30 GMT

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!

Re: Count distinct customers having sales

Anonymous — Wed, 13 Nov 2019 14:32:09 GMT

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))

Re: Count distinct customers having sales

LinhDO — Thu, 14 Nov 2019 03:35:15 GMT

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!