cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors