Linking Tables One to Many - Cumulative Value Issues

[SCPowerBI]

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.

[Greg_Deckler]

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.

[SCPowerBI]

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.

[Greg_Deckler]

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.

[SCPowerBI]

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.

[v-yuezhe-msft]

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

[Greg_Deckler]

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.