cancel
Showing results for
Did you mean:
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?

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
Super User IV

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

Community Support

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.
4 REPLIES 4
Community Support

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.
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!

Regular Visitor

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?

Super User IV

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

Announcements

#### Welcome to the User Group Public Preview

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

#### Microsoft Business Applications Summit sessions

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

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