Multiple filters

P

pmitov

New Member
Joined
Jul 25, 2013
Messages
2
Hello, everyone!

I am new to powerpivot and to mrexcel.com.

And, this is my first post here, so, I would like You to excuse me if You find the post incomplete, inappropriatly titled or kind of boring.

I have the following issue - there is a source table with information about stock deliveries, sales and production (simplified in the sample source table).
There are items, used as components in the production of other complex items. The document number is what determines the connection between the components expense and the complex item production. Any complex item is always produced by the same components and all the components for a specific complex item are always delivered by the same Vendor.
The complex items are being sold after production.

What I need is to calculate the remaining Qty of the complex items, produced by components which are delivered by a specific Vendor.

I figured out that I could filter the items, deliveries by the specified Vendor.
Then use the filtered items to filter the source table just for the production document type (posting group G 99) and take the filtered document numbers.
Then use the filtered document numbers and use them to filter again the source table just for the Positive adj. and Posting group G 99. Thus I would filter the questioned complex items.
But I don't know how to use the filtered data as a new filter.

Any kind of help would be kindly appreciated.

Thanks in advance!

Here is the sample source table and the expected result table. I am using Excel 2010 with Win 7 professional.



Excel 2010
ABCDEFG
The story:
Items 01-09 are components, delivered from different Vendors.
These components are included in the production of other Complex items (12, 34, 89).
The complex items are for sale.
What are we looking for:
The remaining stock Qty of the complex items, produced from the component items which delivered by Vendor V-1.
Let's name it "RQtyV1" - the red column in the result table
Source table
purchasepi 12item 01G 0V-1
purchasepi 12item 02G 0V-1
purchasepi 34item 03G 0V-1
purchasepi 34item 04G 0V-1
negative adj.bom 12item 01G 99
negative adj.bom 12item 02G 99
positive adj.bom 12item 12G 99
negative adj.bom 34item 03G 99
negative adj.bom 34item 04G 99
positive adj.bom 34item 34G 99
purchasepi 89item 08G 0V-2
purchasepi 89item 09G 0V-2
negative adj.bom 89item 08G 99
negative adj.bom 89item 09G 99
positive adj.bom 89item 89G 99
salesi 12 01item 12G 0
salesi 12 02item 12G 0
salesi 34 01item 34G 0
salesi 89 01item 89G 0
Result table
I can deal with all the measures, but the red one - it is way too difficult for a novice like me.
Date is an optional dimension - it could be used in the pivot report or not. I can deal with it too.

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]DocType[/TD]
[TD="align: center"]DocNO[/TD]
[TD="align: center"]DocDate (dd.mm.yyyy)[/TD]
[TD="align: center"]item[/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"]Posting group[/TD]
[TD="align: center"]Vendor[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]25.05.2013[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]25.05.2013[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]18.06.2013[/TD]

[TD="align: right"]20[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]18.06.2013[/TD]

[TD="align: right"]20[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]25.05.2013[/TD]

[TD="align: right"]-10[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]25.05.2013[/TD]

[TD="align: right"]-10[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]25.05.2013[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]18.06.2013[/TD]

[TD="align: right"]-20[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]18.06.2013[/TD]

[TD="align: right"]-20[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]18.06.2013[/TD]

[TD="align: right"]20[/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]31.05.2013[/TD]

[TD="align: right"]80[/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]31.05.2013[/TD]

[TD="align: right"]80[/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]31.05.2013[/TD]

[TD="align: right"]-80[/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]31.05.2013[/TD]

[TD="align: right"]-80[/TD]

[TD="align: center"]27[/TD]

[TD="align: right"]31.05.2013[/TD]

[TD="align: right"]80[/TD]

[TD="align: center"]28[/TD]

[TD="align: right"]28.05.2013[/TD]

[TD="align: right"]-8[/TD]

[TD="align: center"]29[/TD]

[TD="align: right"]10.06.2013[/TD]

[TD="align: right"]-1[/TD]

[TD="align: center"]30[/TD]

[TD="align: right"]20.06.2013[/TD]

[TD="align: right"]-7[/TD]

[TD="align: center"]31[/TD]

[TD="align: right"]05.06.2013[/TD]

[TD="align: right"]-30[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="bgcolor: #DCE6F1, align: center"]item[/TD]
[TD="bgcolor: #DCE6F1, align: center"]Purchase Qty[/TD]
[TD="bgcolor: #DCE6F1, align: center"]Purchase Qty (V-1)[/TD]
[TD="bgcolor: #DCE6F1, align: center"]production[/TD]
[TD="bgcolor: #DCE6F1, align: center"]Sales[/TD]
[TD="bgcolor: #DCE6F1, align: center"]Remaining Qty[/TD]
[TD="bgcolor: #E6B8B7, align: center"]RQtyV1[/TD]

[TD="align: center"]37[/TD]
[TD="bgcolor: #DCE6F1"]item 01[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="bgcolor: #DCE6F1"]item 02[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]
[TD="bgcolor: #DCE6F1"]item 03[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="bgcolor: #DCE6F1"]item 04[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]
[TD="bgcolor: #DCE6F1"]item 08[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-80[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/TD]
[TD="bgcolor: #DCE6F1"]item 09[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-80[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]43[/TD]
[TD="bgcolor: #DCE6F1"]item 12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]44[/TD]
[TD="bgcolor: #DCE6F1"]item 34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]45[/TD]
[TD="bgcolor: #DCE6F1"]item 89[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]

[TD="align: center"]46[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]47[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]48[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Hi pmitov,
Why don't you just use your existing measures to do the remaining work for you? I mean couldn't you just create a new measure RQtyV1 :=[Sales]-[production].
Sounds a little bit too easy but wouldn't that work? Both measures should work in the same context, right?
The data look like coming from Microsoft Dynamics, do they?
Regards,
julianwi
 
Upvote 0
Hi, julianwi,
Thank You for the help!

I have already calculated [Sales]-[production] in the measure Remaining Qty. But it is for all items.
The problem is to isolate those items, which are produced by the components, delivered by Vendor V-1. As can be seen in the result table - in the last (red) column there are values just for item 12 and item 34, which are produced from the components item 01, 02, 03, 04. Item 89 does not have value in that column, cause its components were delivered by Vendor V-2.
I hope this time it is better explained.

And, yes, the data comes from MS Dynamics.

Best regards,
pmitov
 
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
89
Veritan
V
RICH937
  • Question Question
Help! Is it possible to translate Excel Filter Function Formula (@cell) reference into PQE formula?
Replies
7
Views
386
alex78
A
RICH937
  • Question Question
How to create Running Totals on multiple non-date values and conduct filtered analysis on the results. Which Power Tool is the more efficient choice?
Replies
3
Views
437
RICH937
RICH937
S
  • Question Question
Matrix Visual in Power BI
Replies
2
Views
196
shansakhi
S
R
  • Question Question
Using Power query for appending data from table to customized output
Replies
2
Views
290
alansidman
A

Forum statistics

Threads
1,223,952
Messages
6,175,596
Members
452,658
Latest member
GStorm

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