Pivot Table Display - Full order information if it contains one particular item

[devlynsyde]

I am trying to do something that seems like it would be standard, but I can't find anything through searching possibly because I can't figure out proper wording for what I want to do.

I have a table full of orders, and I want the pivot to display only those orders that contain a specific item, but show all items in that order.So for example if someone purchased a t-shirt, jeans, and pair of socks I want it to show:

[FONT=Calibri][SIZE=3][COLOR=#000000]Invoice 3382[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]              T-Shirt[/COLOR][/SIZE][SIZE=3][COLOR=#000000]         1[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]              Jeans[/COLOR][/SIZE][SIZE=3][COLOR=#000000]           8[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]              Socks[/COLOR][/SIZE][SIZE=3][COLOR=#000000]           1[/COLOR][/SIZE][/FONT]

But a typical filter would only display the invoice and the t-shirt.Is it possible to do this with power pivot or vba?

 

[scottsen]

So, if you had a column in your pivot table... say:

TshirtCount := CALCULATE(COUNTROWS(Invoices), Invoices[ProductId] = "TShirtId")

Add it in the values portion of your pivot table. Then you could use the UI to do a VALUE FILTER on Tshirtcount > 0.

You could then just hide the TShirtCount column out of your pivot and dance?

 

[devlynsyde]

So, if you had a column in your pivot table... say:

TshirtCount := CALCULATE(COUNTROWS(Invoices), Invoices[ProductId] = "TShirtId")

Add it in the values portion of your pivot table. Then you could use the UI to do a VALUE FILTER on Tshirtcount > 0.

You could then just hide the TShirtCount column out of your pivot and dance?

This does the same thing as filtering on the specific Item. The formula puts a 1 on any row where the ProductId is in the example TShirt, but not on the other rows of the invoice. So the filter would only show the TShirt in the pivot.

    Invoice             Product              Cost
    1383                Jeans                 4.00
    1383                TShirt                2.00
    1334                TShirt                 2.00

So if the above is a simple representation of the data, I want it to display all of this in the pivot. If it has to be a formula like this I could some what live with it, but ideally I wanted to be able to do this on more than one item.

 

[scottsen]

I did try this out before posting, and it worked fine for me. My filter was at the Transaction level, so the whole transaction was included/excluded, not individual items.

 

[devlynsyde]

I did try this out before posting, and it worked fine for me. My filter was at the Transaction level, so the whole transaction was included/excluded, not individual items.

Ahh.. I was applying the filter incorrectly. This works for a specific item, but now is it possible to feed the DAX a variable item?

 

[scottsen]

Depending on what you want to do... Shouldn't be a problem. Say, filter on a column called "MustBeIncluded" ?

 

[devlynsyde]

Depending on what you want to do... Shouldn't be a problem. Say, filter on a column called "MustBeIncluded" ?

Ideally to pick an item or type an item code and have MUSTBEINCLUDED do the formula you provided for the item code typed in the standard Excel, instead of just for jeans.