View Full Version : PowerPivot Filtering := Frustration ^10 degree!

2014-08-04, 03:00 PM
I am a little stumped by something that seems fairly easy to do! I am working with PowerPivot, and have a large list of items. I can return the data easily enough to a pivot table, but what if I want to filter it first. I have looked thru multiple DAX formulas, and see the filters and the calculate and such, but here is the problem, (ref table below). All I want to do is know how may item As I have in multiple stores, and only count it once:

Item ID

Store A Quantity

Store B Quantity









Seems easy enough, how many item "A"s do I have? I have 3, in two stores, but I still only have three. I would like to know I have three of the Item "A"s on one report, and would like to know that Item "A" lies in two stores on another report. But How can I put that in PowerPivot? This may seem visually easy, but in the PowerPivot, I have hundreds of thousands of items, spanning across up to eight stores.

Any Ideas? Would be greatly appreciated...


Bob Phillips
2014-08-04, 04:02 PM
I am missing the problem here. If you have 1 in store A, and 2 in store B, they will show in one row of the pivot, row totals will show that you have 3. Or just show Item and Quantity in your pivot.

2014-08-04, 04:18 PM
Actually, there is one more problem that I forgot to mention. I have to keep all inventory levels the same at all stores. So, if I have 2 Item "A"s in store B, then I also need 2 in Store A. Like I said, I am stumped. The solution seems easy enough, just can't seem to grab the line it is dangling on.

Bob Phillips
2014-08-04, 05:05 PM
If I couldn't see the problem in the first case, adding another variable is not exactly clarifying it.

2014-08-04, 06:01 PM
My goal would be to produce a report that would say "Here are the items that hold a quantity that is not the same as the other stores". If I sum up in a pivot table, it will produce a list of items that are in the thousands. If they click on one after another, yes, it would show them the correct data, but there would be many that would not have to change (due to them only being in one store). I would like to filter those out because they are only in one store, so the list is not as long. Another problem I just ran into, I just noticed that some stores have zero while other may have blank. Unfiltered, there are 105,000 items. What I tried to do, was to put an average of the stores in instead of the total (for the row total in the Pivot), and then find a way to see if the store total was equal to the average, and if not, put in the text "review" (or flag it in some way).

Bob Phillips
2014-08-04, 07:41 PM
Another variable :)

I'm going to take a shot at it.

You could add a couple of calculated columns in your Power Pivot model, to test the store for A or B, like

StoreA: =IF([Store] = "A", [Quantity[], 0)
StoreB: =IF([Store] = "B", [Quantity[], 0)

and include them in the pivot table, then add measure


and filter by FALSE values in that measure.

2014-08-04, 07:56 PM
ooo (as I raise my paw to my mouth), you are onto something there. What about mutliple stores? I have 8 total. Can you put all of them in one calc? I can see it in the first part (IFF I think?), but a True/false on the second, with 8 stores? I think I may have to use an IFF there as well, then combine. Let me give this some thought. Either way, you opened a door there sir, thanks~

Bob Phillips
2014-08-05, 12:22 AM
Power Pivot doesn't have IFF!

Have separate bins for each store, even 8 is no problem, but the Measure is a tad messier. You could do it with AND


this is for 3 stores, but is easily extended for 8.

I think there must be a better, tidier way, but I would want to see the PP model before I tried any further.

2014-08-05, 12:06 PM
whoops, your correct, wrong code, only one F for this one :) It is going to be messy, and after dabbling with it more, think I may have to break each individual store into its own query/tab since I have to put in item attributes anyhow. I will toy with it more, see if I can change some things around and consoliate for the end report. With that said, I will say this one is complete. Thank you very much for you assistance Mr. Phillips. It was just what I needed to change my perspective.