Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |