Forum Discussion
YTD DistinctCount not totaling correctly
Hi,
The problem is in Feb. The # adds upto 426 though it should add up to 427. It means that there is a member being considered both in new and old in that month itself. Check your data. Make the corrections mentioned in my previous post.
Hello mclawler ,
Thank you for sharing the details! Just to confirm , the super user Ashish_Excel answer is absolutely correct. The key issue lies in the relationship type between your tables.
Please change the relationship from many-to-many to many-to-one between Heloc3MonthAdvances and DateTable. Once that's corrected, the measures suggested by the super user will work as intended and will give you the correct YTD total.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! |
Regards,
B Manikanteswara Reddy
Regards,
B Manikanteswara Reddy
Hi there, thanks for the support. However, I do need it to be additive and incrementally add each month for YTD. Like an Excel spreadsheet would incrementally add each tab/month data to the next. The solution you have provided gives me 1,064 Total for April YTD # which is incorrect.
The distinct count Total for April YTD # column should be 1,817.
Existing HELOC = 1,679
New HELOC = 138
Details below for reference:
Jan Total = 451
Existing HELOC = 421
New HELOC = 30
Feb Total = 427
Existing HELOC = 393
New HELOC = 34
Mar Total = 441
Existing HELOC = 395
New HELOC = 46
Apr Total = 498
Existing HELOC = 470
New HELOC = 28
April YTD # Column Total: 451 + 427 + 441 + 498 = 1,817
Please advise. Thank you for all your help!