Forum Discussion
Linking Tables One to Many - Cumulative Value Issues
Can you post some sample data, it sounds like you need an Item table, or you need to use your Sales Order # from your Sales Order table. Or, I can't quite say, some sample data would help me recreate it.
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.
OK, so what I did was create your queries in an example. I related Sales Orders (Query 1) and Sales Quantities (Query 3) on Sales Order. Then I created a matrix with Sales Order from Sales Order table, Item from Sales Quantities table and Sales from Sales Quantities table, seemed to work swell. Let me know if you want me to post some screen shots.
Yes I would appreciate it, I have searched for info on merging the queries. I need something similar to a sum-if in excel basically sum if sales order, item.
Hi SCPowerBI,
I create a Matrix visual and Card visual using your sample data, does the following screenshot display your expected result?
When I filter item to A, the two visuals return correct values as follows. For more details, please check the attached PBIX file.
Thanks,
Lydia Zhang