Forum Discussion
Table Not Totalling Correctly
Hi Anonymous ,
If you can inspect your data in the query editor, under the view tab you can turn on column profile, column distribution and column quality, and see if all the values in the reference number column are really unique.I would start there. Normally what would cause this discrepancy is that you have the same reference number present in two different months/years. In the normal count they will be counted twice, in the distinct count once.
You can see that the sum of distinct count does not add up to the total in the second table. That is because it is distinct counting each month/year, as well as the total.
For example with this dummy data:Month reference ID January a January b February a February c March b March c The counts would look like this:
Month Distinct Count Count January
2 2 February 2 2 March 2 2 Total 3 6 Because there are two distinct values in january, two in february, two in march, but only 3 in total because of the duplicate values.
If you want to troubleshoot this you could create a Measure = COUNTROWS(Table), and put the reference column and the countrows measure into a table visual. There should be one or two reference columns where the countrows measure is higher than one, meaning that the column is not unique.
I hope this helps.
Hi Anonymous ,
If you can inspect your data in the query editor, under the view tab you can turn on column profile, column distribution and column quality, and see if all the values in the reference number column are really unique.
I would start there. Normally what would cause this discrepancy is that you have the same reference number present in two different months/years. In the normal count they will be counted twice, in the distinct count once.
You can see that the sum of distinct count does not add up to the total in the second table. That is because it is distinct counting each month/year, as well as the total.
For example with this dummy data:
| Month | reference ID |
| January | a |
| January | b |
| February | a |
| February | c |
| March | b |
| March | c |
The counts would look like this:
| Month | Distinct Count | Count |
|
January |
2 | 2 |
| February | 2 | 2 |
| March | 2 | 2 |
| Total | 3 | 6 |
Because there are two distinct values in january, two in february, two in march, but only 3 in total because of the duplicate values.
If you want to troubleshoot this you could create a Measure = COUNTROWS(Table), and put the reference column and the countrows measure into a table visual. There should be one or two reference columns where the countrows measure is higher than one, meaning that the column is not unique.
I hope this helps.