cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
depple Regular Visitor
Regular Visitor

Filtering

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:

 

StoreProductCategoryProductSales
Store AAlfaProduct A - Single0
Store AAlfaProduct A - Multipack4
Store ABravoProduct B - Single0
Store ABravoProduct B - Multipack0
Store BAlfaProduct A - Single0
Store BAlfaProduct A - Multipack8
Store BBravoProduct B - Single10
Store BBravoProduct B - Multipack0

 

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
StoreAlfaBravo
Store A 0
 
 I want the report to look like this:
 
 ProductCategory
StoreAlfaBravo
Store A40

 

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

 

/depple

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Filtering

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())

 

 

 

 

Capture.PNG

 

Please find the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Highlighted
Community Support Team
Community Support Team

Re: Filtering

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())

 

 

 

 

Capture.PNG

 

Please find the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
depple Regular Visitor
Regular Visitor

Re: Filtering

@v-frfei-msft 

 

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

 

/depple