Forum Discussion
Linking Tables One to Many - Cumulative Value Issues
So I have three queries with Sales Order as the unique value
Query 1
| Sales Order | Weight | Fill Rate |
| 101 | 40,000 | 97.20% |
| 102 | 35,000 | 96.80% |
| 103 | 35,000 | 98.00% |
| 104 | 41,000 | 99.00% |
| 105 | 42,000 | 95.23% |
| 106 | 40,000 | 94.60% |
| 107 | 37,000 | 86.00% |
Query 2
| Sales Order | Item | Units Ordered |
| 101 | A | 10 |
| 101 | B | 15 |
| 101 | C | 10 |
| 101 | D | 5 |
| 102 | A | 5 |
| 102 | D | 8 |
| 103 | A | 6 |
| 103 | B | 10 |
| 103 | C | 10 |
| 103 | D | 9 |
| 104 | B | 8 |
| 104 | C | 7 |
| 104 | D | 7 |
| 105 | B | 10 |
| 105 | C | 10 |
| 106 | A | 20 |
| 107 | A | 20 |
| 107 | D | 25 |
Query 3
| Sales Order | Item | Sales |
| 101 | A | $30.00 |
| 101 | B | $35.00 |
| 101 | C | $60.00 |
| 101 | D | $70.00 |
| 102 | A | $20.00 |
| 102 | D | $22.00 |
| 103 | A | $27.00 |
| 103 | B | $34.00 |
| 103 | C | $11.00 |
| 103 | D | $9.00 |
| 104 | B | $14.00 |
| 104 | C | $23.00 |
| 104 | D | $7.00 |
| 105 | B | $40.00 |
| 105 | C | $22.00 |
| 106 | A | $47.00 |
| 107 | A | $34.00 |
| 107 | D | $28.00 |
I have a Card with a sum of sales, and a column chart visualization with units ordered, units shipped with several drilldown levels starting with brand>product family>product group>item. When filtering by item the sum of "sales" appears to be taking the sum or all orders with that item so if I have it filtered by item A, instead of giving me a sum of sales for item A it is giving me a sum of total sales orders that have item A included. So instead of the sum being $158 for item A, it is coming back as $427.
So, what you could do is a couple things. One, you could create 2 slicers, one for Sales Order and one for Item. Slice by Sales Order and then slice by Item. You could also consider joining your Query 2 and Query 3 together based upon Sales Order and Item (composite key). I'll have to spend some time trying to recreate your issue but I get the sense from your description that you are not filtering on Sales Order so Sales Order is not a contextual filter so therefore it does not play into the calculations of the sums.