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.
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