cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Datadiver001
Regular Visitor

Diffrence in unique values in table and in summarized total

Hey,

 

For one of my data models I'm finding a difference in the unique values within a table.

 

I want to know the number of unique customers that has a subscription and when this subscription is ending. A client can have multiple subscriptions. I want to know how many unique customers I have this year (table) and in which month the subscription is ending (graph). 

 

I've made a table that has the customers IDs and the enddate of their subscription. I use a date table and a relationship with my fact table (subscriptions) to convert the specific end dates (i.e. 05-06-2021) to their respective months (May/Mei in my language). In the formatting tab I select to summarize by Values (distinct/unqiue).

 

If I add the values of each month the total is 1.602. And not the 1367 which the table summarizes? 

 

https://pasteboard.co/JRO3Ra4.jpg 

 

I'm finding differences in more slides now. How do I find the number of unique customers per month?

I've tried using a measure with DISTINCTCOUNT but ran into the exact same results...

 

Any ideas?

 

 

2 ACCEPTED SOLUTIONS

@Datadiver001 , The formula I suggested will force a total of rows.

Simple DISTINCTCOUNT (Table[customer id]), will be recalculated at grand total and subtotal level 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

V-pazhen-msft
Community Support
Community Support

@Datadiver001 

Try use Hasonevalue(), something like:

 

Measure = IF(HASONEVALUE(Table[customer id]),DISTINCTCOUNT(Table[custome id]), calculate(DISTINCTCOUNT (Table[customer id]), all(table))
 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
V-pazhen-msft
Community Support
Community Support

@Datadiver001 

Try use Hasonevalue(), something like:

 

Measure = IF(HASONEVALUE(Table[customer id]),DISTINCTCOUNT(Table[custome id]), calculate(DISTINCTCOUNT (Table[customer id]), all(table))
 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

View solution in original post

amitchandak
Super User IV
Super User IV

@Datadiver001 , You have to create a measure like this

 

sumx(values(Table[Month year]), calculate(DISTINCTCOUNT (Table[customer id])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi @amitchandak I've tried this solution and it seems right but now the total of the table are corresponding with the total of the values in each of the rows. Is this a normal result? And is there a work around for this?

@Datadiver001 , The formula I suggested will force a total of rows.

Simple DISTINCTCOUNT (Table[customer id]), will be recalculated at grand total and subtotal level 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors