Forum Discussion
Adding a calculated column to a table that adds from a separate table
Hello!
I have two tables both have a relationship with one another based on the date. (One to many Relationship)
- Table 1 (Many Dates)
- Table 2 (One Date)
I have created the table visualization below and you can see my calculation above which simply adds each column together. The second to last column is from my Table 2 which I have added to this formula using the related function..
You can see that the total that is summarized is incorrect, if it were correct each column added together would equal: 86,325.84 NOT 13,310,201.39
I simply need to know how to add this column since it comes from a different table.
thanks!
Austin
Hello Anonymous
The problem you are running into is because table 2 has the same date many times your # from table 1 is being duplicated.
Could you try this for me. Add the following measures to your model, each one sums a column from a table.
Cimarex Amt = SUM ( 'LICC Data'[Cimarex] ) Apache Amt = SUM ( 'LICC Data'[Apache] ) XTO Amt = SUM ( 'LICC Data'[XTO] ) BTA Amt = SUM ( 'LICC Data'[BTA] ) EDG Amt = SUM ( 'LICC Data'[EDG] ) RKI Amt = SUM ( 'LICC Data'[RKI] ) TABULA RASA Amt = SUM ( 'LICC Data'[TABULA RASA] ) TRUCK TOTAL Amt = SUM ( 'LICC Data'[TRUCK TOTAL] ) DB Bulk Amt = SUM ( 'TEST'[DB Bulk] )
Then we add a measure to sum them up
Total DB = [Cimarex Amt] + [Apache Amt] + [XTO Amt] + [BTA Amt] + [EDG Amt] + [RKI Amt] + [TABULA RASA Amt] + [TRUCK TOTAL Amt] + [DB Bulk Amt]
Then, in your visual, pull the date in from Table2 (the one with the single date) and pull in those measures.
2 Replies
- jdbuchanan71
Super User
Hello Anonymous
The problem you are running into is because table 2 has the same date many times your # from table 1 is being duplicated.
Could you try this for me. Add the following measures to your model, each one sums a column from a table.
Cimarex Amt = SUM ( 'LICC Data'[Cimarex] ) Apache Amt = SUM ( 'LICC Data'[Apache] ) XTO Amt = SUM ( 'LICC Data'[XTO] ) BTA Amt = SUM ( 'LICC Data'[BTA] ) EDG Amt = SUM ( 'LICC Data'[EDG] ) RKI Amt = SUM ( 'LICC Data'[RKI] ) TABULA RASA Amt = SUM ( 'LICC Data'[TABULA RASA] ) TRUCK TOTAL Amt = SUM ( 'LICC Data'[TRUCK TOTAL] ) DB Bulk Amt = SUM ( 'TEST'[DB Bulk] )
Then we add a measure to sum them up
Total DB = [Cimarex Amt] + [Apache Amt] + [XTO Amt] + [BTA Amt] + [EDG Amt] + [RKI Amt] + [TABULA RASA Amt] + [TRUCK TOTAL Amt] + [DB Bulk Amt]
Then, in your visual, pull the date in from Table2 (the one with the single date) and pull in those measures.