Forum Discussion
Total in table sums incorrectly
Hi all,
I have a table that shows the number of members for a loyalty card scheme sliced by location:
The weird thing is that the total is correct -- I know that there's 48364 members in total. But if I export the data from the table and sum them in excel, the table actually totals at 55929. So for some reason, it's not calculating the number of members in each area correctly.
I have a feeling the problem is caused by the relationships in my data model (which admittedly is messy!). I have a dataset 'Members' that contains membership info, which is linked to another dataset 'Geographies' that contains the names of the gepgraphical regions I want to slice by. These two datasets are linked by postcode. I've highlighted the datasets and fields I want to connect in red. The arrow points to the relationship.
I've tried changing the direction and cardinality of the relationship, which changes the numbers, but still the total is shown in the table is incorrect. I've also tried indirectly linking the two by linking Members > LatestPopulation > Gepgraphies and fiddling around with that relationship too, but for everything I try the totals still don't sum correctly!
Any ideas as to what's causing this? Any help much appreciated as I'm completely out of ideas!
Thanks!
7 Replies
Anonymous Is it maybe a situation where a member is part of more than one loyalty card program? If you add the numbers that show up in the column in your table visualization you get 55,929.
This looks like the reverse of the measure totals problem but related. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907Hi Greg_Deckler
Thanks very much for your reply. You're right, it is the reverse of the common measures problem -- it's the total in the table that's correct and the individual rows that are incorrect. There's definitely only 48,364 members, not 55,929 members. So it seems to be miscalculating the number of members in each electoral ward. It's strange, as there's only one row per member. Each member has a postcode and that postcode is what links it to the electoral ward in the second dataset. It's a one-to-one match on postcode, so I can't figure out why it would be miscaluting the numbers in each ward.
I've had a play around with your Final Word measures, but because it's the reverse of the typical problem I'm not sure how to apply it in my case.
Any ideas as to what I could try?
Help much appreciated!
Thanks again
Anonymous Well technically between geographies and members tables you have a many to many relationship. So if you are not using your bridge table in your visual then you could get weird results I am thinking. Can you mock up some sample data that exhibits this issue?
