Filtering

[depple]

Earlier, I have received great help with this issue, but it turned out my table was more complex than I realised, and the solution ceased working as desired. I have tried to tweak on my measures, but to help avail.

I wish to create a report where stores with 0 sales of a product is listed (regardless of packaging).

The data table has a structure like this:

Store	ProductCategory	Product	Sales
Store A	Alfa	Product A - Single	0
Store A	Alfa	Product A - Multipack	4
Store A	Bravo	Product B - Single	0
Store A	Bravo	Product B - Multipack	0
Store B	Alfa	Product A - Single	0
Store B	Alfa	Product A - Multipack	8
Store B	Bravo	Product B - Single	10
Store B	Bravo	Product B - Multipack	0

I have two measures:

1. 

ZeroSale = VAR StoresWithZeroProduct =

FILTER (

VALUES ( 'Table 1'[Store] );

NOT ISEMPTY ( FILTER ('Table 1'; [SumDpak] = 0 ) )

)

RETURN

CALCULATE( [SumDpak]; KEEPFILTERS ( StoresWithZeroProduct))

2. 

SumDpak = CALCULATE(SUM('Table 1'[Sales]);ALLEXCEPT('Table 1';'Table 1'[ProductCategory];'Table 1'[Store]))

With my current measures the report looks like this:

	ProductCategory
Store	Alfa	Bravo
Store A		0

 I want the report to look like this:

	ProductCategory
Store	Alfa	Bravo
Store A	4	0

Any suggestions on what I must change to get to the desired result?

/depple

[v-frfei-msft · Accepted solution]

Hi @depple ,

We can create two calculated columns to work on it.

Column = var sum1= CALCULATE(SUM('Table 1'[Sales]),ALLEXCEPT('Table 1','Table 1'[Store],'Table 1'[ProductCategory]))
return
IF(sum1=0,'Table 1'[Store],BLANK())

New store = IF('Table 1'[Store] in VALUES('Table 1'[Column]),'Table 1'[Store],BLANK())

Please find the pbix as attached.

Regards,

Frank

[depple]

@v-frfei-msft 

Thanks a million! It worked perfect, and it also sorted out some other difficulties that I had.

/depple