Forum Discussion

cdawidow's avatar
cdawidow
Icon for Helper III rankHelper III
6 years ago

Total Sales is duplicate

 

Hi guys, I am wondering if there is a way to fix this.  My total sales calculation is right, but my sales by contract name is duplicated. Any fix> please see below for my model.

 

 

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    cdawidow - 9 times out of 10 that's an issue with the relationship. It's almost certainly caused by having many-to-many relationships. I would seek to eliminate those using bridge tables most likely.

    • cdawidow's avatar
      cdawidow
      Icon for Helper III rankHelper III
      6 years ago

      Thanks for the help!

      I just have one unique column in my tables (Contract Name) which is distinct to only its table.  How would I create a bridge table that could allow me to calculate total sales by contract name?  Thanks in advance

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    cdawidow , You can create a bridge table like.

    contract Name = distinct(union(all(Hope[contract Name]),all(contracts[contract Name])))

     

    The same way you can create other dimensions/master/bridge tables

    • cdawidow's avatar
      cdawidow
      Icon for Helper III rankHelper III
      6 years ago

      Well I just changed my model hopefully you can see below.  What I did was I concatenated both the sales and contract tables to create a relationship but still even with this bridge I created my total values were still wrong

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        Contracts (2) stores unique names of contracts as much as I understand. Why do you then have many-to-many between Contracts (2) and Contracts? Second question, why is the connection between Contracts and Sales many-to-many with bi-dir filtering? Bi-dir filtering and many-to-many are EXTREMELY DANGEROUS constructs and should be used with UTMOST care (please refer to Power BI documentation). In other words, you should NOT use them if you're just starting learning Power BI.

        If you want to create simple, fast and easy to understand models, please read about dimensional modeling that also is the foundation of Power BI modeling. The documentation on Power BI is a good place to start. There are tons of vids on YT as well that explain how you should model data.

        If you neglect this part of learning, you'll be sorry rather sooner than later. You run the risk of producing wrong numbers without ever realizing it.