Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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 i...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi 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

 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Greg_Deckler , sorry I'm not quite sure what you mean (fairly new to Power BI!). What's a bridge table?

     

    Thanks!

    H

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      5 years ago

      Anonymous Bridge table:

       

      Table 1 *<---1 Bridge Table 1 --> * Table 2

       

      Basically a bridge table is a distinct (unique) list of values in the columns that make up the relationship between tables 1 and 2. You can create it in Power Query or in DAX. In DAX it is:

       

      Bridge Table = 
  DISTINCT(
    UNION(
      SELECTCOLUMNS('Table 1',"Column",[Column]),
      SELECTCOLUMNS('Table 2',"Column",[Column])
    )
  )
      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Greg_Deckler 

         

        I see! I've looked into this a bit more now and had a play around. I seem to have solved the problem -- you're right it's caused by the many-to-many cardinality in what's a poorly structured data model. I've read up on model structures and rejigged my model into a star schema and it seems to have solved the problem (so far!)

         

        Thanks for your help!

        H