Forum Discussion
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?
Anonymous , The formula I suggested will force a total of rows.
Simple DISTINCTCOUNT (Table[customer id]), will be recalculated at grand total and subtotal level
Anonymous
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
- amitchandak
Super User
Anonymous , You have to create a measure like this
sumx(values(Table[Month year]), calculate(DISTINCTCOUNT (Table[customer id])))
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?
- amitchandak
Anonymous , The formula I suggested will force a total of rows.
Simple DISTINCTCOUNT (Table[customer id]), will be recalculated at grand total and subtotal level
Anonymous
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.