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.
