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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Poupouh
Frequent Visitor

Distinctcount excluding negative number through time

Hello!

I am quite new to Power Bi and I am currenlty experiencing trouble with the DISTINCTCOUNT  function. 

What I am trying to do is quite easy, counting the distinct number of customer excluding the negative and nul numbers. 

My formula is the follwing: 

 

calculate( distinctcount(Database[Parent name]),filter('Database','Database'[transaction sales]>=1), filter('calendar','Calendar'[Year Type]="This Year"))
 
My expected final visual must be as followed
Picture1.png
However for some reason Power Bi count the customers as long as they have a positive amount once. Which results in a deviation in the final number of customers per month. 
Picture2.png
I don't know how to chnage my formula to make Power Bi iterate on a monthly basis not on the total. 
 
If any body could be of assistance on this point this would be really really helpful. 
Thank you in advance 😣
 
 
1 ACCEPTED SOLUTION
Poupouh
Frequent Visitor

Thank you for the reference. I managed getting the good number via a If and Summarize function. 

Again, thank you for your help!

View solution in original post

2 REPLIES 2
Poupouh
Frequent Visitor

Thank you for the reference. I managed getting the good number via a If and Summarize function. 

Again, thank you for your help!

amitchandak
Super User
Super User

@Poupouh , distinctcount will be calculated again at grand total so it will not be a sum of rows

 

refer: https://www.youtube.com/watch?v=ufHOOLdi_jk

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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