Forum Discussion
Linking Tables One to Many - Cumulative Value Issues
Hi All,
I have two queries that I have linked as a 1 to many ratio by Sales Order # as the unique identifier.
Query 1 - Has sales order for each individual row without "sales value"$ amount but other metrics.
Query 2 - Has multiple rows per sales order with sales value $ listed per each item on the sales order.
When I filter through each visualization, the sum of sales value appears to filter on the total value of the sales order with any sales order that has that item listed.
Instead I would like to have it just filter on the total value for sales for that item.
Any help would be greatly appreciated.
6 Replies
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.
