Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LinhDO
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
Anonymous
Not applicable

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

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors