Pivot creating nonexistent lines

M

msciblo

New Member
Joined
Jul 30, 2012
Messages
1
I have three tables; Line Items, Order Lines and Order Payment Lines. I would like to create a pivot using Payment Type as a slicer [I filtered to remove Reships] and the pivot contains Order Number, Product Description, Last Name, First Name and State as Row Labels with Line Total and Shipping Total as Values. I created relationships between Line Items and Order Lines on Order Number and Line Items and Order Payment Lines on Order ID.

Here are the tables:

Line Items:

[TABLE="width: 790"]
<tbody>[TR]
[TD]OrderNumber[/TD]
[TD]Ship Date[/TD]
[TD]Product Pin[/TD]
[TD]Product Description[/TD]
[TD]Units Sold[/TD]
[TD]Line Total[/TD]
[TD]Order ID[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1-Jan[/TD]
[TD]1[/TD]
[TD]Apples[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2-Jan[/TD]
[TD]2[/TD]
[TD]Bananas[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2-Jan[/TD]
[TD]2[/TD]
[TD]Apples[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2-Jan[/TD]
[TD]3[/TD]
[TD]Cherries[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1-Jan[/TD]
[TD]3[/TD]
[TD]Cherries[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3-Jan[/TD]
[TD]1[/TD]
[TD]Apples[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5-Jan[/TD]
[TD]4[/TD]
[TD]Dates[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]8-Jan[/TD]
[TD]3[/TD]
[TD]Cherries[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1-Jan[/TD]
[TD]1[/TD]
[TD]Apples[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1-Jan[/TD]
[TD]2[/TD]
[TD]Bananas[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1-Jan[/TD]
[TD]3[/TD]
[TD]Cherries[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1-Jan[/TD]
[TD]3[/TD]
[TD]Cherries[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1-Jan[/TD]
[TD]4[/TD]
[TD]Dates[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]800[/TD]
[/TR]
</tbody>[/TABLE]

Order Lines:

[TABLE="width: 626"]
<tbody>[TR]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]State[/TD]
[TD]Order ID[/TD]
[TD]OrderNumber[/TD]
[TD]Shipping Total[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]Emmitt[/TD]
[TD]PA[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Jones[/TD]
[TD]Julio[/TD]
[TD]NJ[/TD]
[TD]200[/TD]
[TD]2[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Johnson[/TD]
[TD]Chad[/TD]
[TD]NY[/TD]
[TD]300[/TD]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Andrews[/TD]
[TD]Julie[/TD]
[TD]DE[/TD]
[TD]400[/TD]
[TD]4[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Jackson[/TD]
[TD]Andrew[/TD]
[TD]PA[/TD]
[TD]500[/TD]
[TD]5[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Boyd[/TD]
[TD]Brandon[/TD]
[TD]WI[/TD]
[TD]600[/TD]
[TD]6[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Prime[/TD]
[TD]Optimus[/TD]
[TD]PA[/TD]
[TD]700[/TD]
[TD]7[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Jackson[/TD]
[TD]Michael[/TD]
[TD]DE[/TD]
[TD]800[/TD]
[TD]8[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

Order Payment Lines:

[TABLE="width: 338"]
<tbody>[TR]
[TD]Order Number[/TD]
[TD]Payment Method[/TD]
[TD]Order ID[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Credit Card[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Credit Card[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Credit Card[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Check[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Reship[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Reship[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Reship[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Credit Card[/TD]
[TD]800[/TD]
[/TR]
</tbody>[/TABLE]

The pivot below is what my attempts have yielded, however Order Number 1 only has Line Item but the pivot shows lines for all Product Descriptions. Order Numbers 5, 6 and 7 are all reships so I would like them filtered out entirely.


Payment Method slicer [filtered to allow only Check and Credit Card]

[TABLE="width: 715"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Values[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OrderNumber[/TD]
[TD]Product Description[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]State[/TD]
[TD]Sum of Line Total[/TD]
[TD]Sum of Shipping Total[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apples[/TD]
[TD]Smith[/TD]
[TD]Emmitt[/TD]
[TD]PA[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bananas[/TD]
[TD]Smith[/TD]
[TD]Emmitt[/TD]
[TD]PA[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cherries[/TD]
[TD]Smith[/TD]
[TD]Emmitt[/TD]
[TD]PA[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Dates[/TD]
[TD]Smith[/TD]
[TD]Emmitt[/TD]
[TD]PA[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apples[/TD]
[TD]Jones[/TD]
[TD]Julio[/TD]
[TD]NJ[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bananas[/TD]
[TD]Jones[/TD]
[TD]Julio[/TD]
[TD]NJ[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Cherries[/TD]
[TD]Jones[/TD]
[TD]Julio[/TD]
[TD]NJ[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Dates[/TD]
[TD]Jones[/TD]
[TD]Julio[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apples[/TD]
[TD]Johnson[/TD]
[TD]Chad[/TD]
[TD]NY[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bananas[/TD]
[TD]Johnson[/TD]
[TD]Chad[/TD]
[TD]NY[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cherries[/TD]
[TD]Johnson[/TD]
[TD]Chad[/TD]
[TD]NY[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Dates[/TD]
[TD]Johnson[/TD]
[TD]Chad[/TD]
[TD]NY[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Apples[/TD]
[TD]Andrews[/TD]
[TD]Julie[/TD]
[TD]DE[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bananas[/TD]
[TD]Andrews[/TD]
[TD]Julie[/TD]
[TD]DE[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Cherries[/TD]
[TD]Andrews[/TD]
[TD]Julie[/TD]
[TD]DE[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Dates[/TD]
[TD]Andrews[/TD]
[TD]Julie[/TD]
[TD]DE[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Apples[/TD]
[TD]Jackson[/TD]
[TD]Andrew[/TD]
[TD]PA[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Bananas[/TD]
[TD]Jackson[/TD]
[TD]Andrew[/TD]
[TD]PA[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Cherries[/TD]
[TD]Jackson[/TD]
[TD]Andrew[/TD]
[TD]PA[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Dates[/TD]
[TD]Jackson[/TD]
[TD]Andrew[/TD]
[TD]PA[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Apples[/TD]
[TD]Boyd[/TD]
[TD]Brandon[/TD]
[TD]WI[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Bananas[/TD]
[TD]Boyd[/TD]
[TD]Brandon[/TD]
[TD]WI[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Cherries[/TD]
[TD]Boyd[/TD]
[TD]Brandon[/TD]
[TD]WI[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Dates[/TD]
[TD]Boyd[/TD]
[TD]Brandon[/TD]
[TD]WI[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Apples[/TD]
[TD]Prime[/TD]
[TD]Optimus[/TD]
[TD]PA[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Bananas[/TD]
[TD]Prime[/TD]
[TD]Optimus[/TD]
[TD]PA[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Cherries[/TD]
[TD]Prime[/TD]
[TD]Optimus[/TD]
[TD]PA[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Dates[/TD]
[TD]Prime[/TD]
[TD]Optimus[/TD]
[TD]PA[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Apples[/TD]
[TD]Jackson[/TD]
[TD]Michael[/TD]
[TD]DE[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Bananas[/TD]
[TD]Jackson[/TD]
[TD]Michael[/TD]
[TD]DE[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Cherries[/TD]
[TD]Jackson[/TD]
[TD]Michael[/TD]
[TD]DE[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Dates[/TD]
[TD]Jackson[/TD]
[TD]Michael[/TD]
[TD]DE[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]49[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
[Sum of Shipping Total] is a measure that comes from a lookup table. In that case, the data will not get filtered.

I am not sure about the correct explanation, but I guess this will be something along "for every Line Item, there exists, by definition, a related Shipping Total".

In order for the filtering to work, you will have to use a measure that is based on your fact table (Line Items).

Something like this:
SUMX(LineItems, RELATED(Orders[Shipping Total]))
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Filter Table Visualization to Return All Valid Line Items
Replies
2
Views
142
Veritan
V
J
  • Question Question
SPC In PowerPivot
Replies
0
Views
472
JB2020
J
S
  • Solved
Power BI Line chart visual
Replies
3
Views
402
shansakhi
S
T
  • Solved
Append data from one column to another in power query
Replies
3
Views
7K
Kerryx
K
T
  • Solved
Power Query - Units per Month
Replies
2
Views
448
therealjdj
T

Forum statistics

Threads
1,226,858
Messages
6,193,388
Members
453,792
Latest member
Vic001

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top