Powerpivot - Filters not working even with measure on values! Help

[bmbhelper]

Hi,

I apologize if this is not the right place.

Here's my problem. I am trying to find a way that will let our sales team know what products an account is ordering and not ordering. 

I created the following measure. Voids to fill:=if(sum('Master Table'[Sales])=0,1,2)

If the sum of selection/slicer is 0 then just return a 1 if it's not zero (they have ordered) return a 2.

I have a raw data dump that uploads to powerpivot that contains customer name and customer #. I have a second table that combines all customers into a parent company. In the example below the different Stop and Shops accounts are related to the Stop and Shop parent.

 

See below:

 

When i drag the Voids to fill measure in the values and filter for Stop n Shop Parent and look at the Almond Butter category for example, the field works as intended. It tells me what almond butters they haven't bought and which ones they have.

See pic:

Now this is where the issue comes. Although at the high level the Lightly Toasted Almond Butter, Unsalted item is ordered, i know that probably not all stores order it, just some. I want to see what stores don't order it. I'll filter for that sku and bring back the customer name (level 0 field in raw data) to see at the store level.

When i filter for stop n shop (related table) and bring in low level detail from raw data, the stop n shop filter is ignore and i basically get all customer names low level ignoring the filters, i've talked to a few IT folks here and they told me this is called a 'Full Outer' ?

Can you guys help me understand what is causing this issue? Why are the filters being ignored? This only happens with that measure, if i bring in straight sales it works fine. Something about the field, i can look at things at the parent level fine but low level from raw data it just doesn't work.

Any help?

Thanks,

[Ashish_Mathur]

Hi,

Please share the link from where i can download your data and show your expected result there.

[bmbhelper]

Hi Ashish,

Sorry for the delay. See below is a link to a sample file. I have a table that should aggregate to customer names to a single account name.

File Here

In the file, I can bring the voids to fill field, filter almond butter category, almond butter skus accross and account name in the rows. The measure works, it returns a 1 if that account name doesn't order a specific almond butter sku.

That is at the aggregate level, let's say I want to see if there are certain Ace Natural accounts that don't order, I bring Account Name to the filter field and filter Ace Natural, I then bring customer name to the row fields and this is where the error happens.

The pivot shows accounts that not related to ace natural, even though i have a filter being applied. If I drag Qty to the values field it works perfectly.

I hope this helps clarify my issue. Thank you for your time!

[Ashish_Mathur]

Hi,

If your question is, how can i get the correct list in the row labels without dragging the Voids to fill measure in the value area section, then that is not possible.

Please post back if my assumption is incorrect.

[bmbhelper]

Hi,

I want to drag the voids field to values, but when i do the row fields is not filtered correctly by the filters applied. It ignored that i have 'ace natural' filtered for example and it shows other accounts not related to Ace.

[Ashish_Mathur]

Hi,

I do not face that problem.  When i filter Customer Name on Ace Naturals, the Pivot goes blank.