Filter on total and not subcategory

[giuliapiazza94]

Hi guys,

I've a problem. I've a matrix like this:

I'd want to filter customers with delta sales (total) < 0 but if I set the page filter

I get

So, it removes all delta sales > 0, while I want them too because I'd want to filter only total and not subcategory.

Have you any ideas?

Thank you all 😁

[v-yetao1-msft · Accepted solution]

Hi @giuliapiazza94 

Create a measure like this :

Measure = 
var _a=CALCULATE([Delta sales],ALLEXCEPT(Foglio1,Foglio1[Customer])) 
var _b=[Delta sales]
return IF(_a>0,_b,BLANK())

Then add the measure in your Matrix visual , you will get a result like this :

In visual filter , set measure is not blank , the final result is as shown :

I have attached my pbix file , you can refer to it .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[giuliapiazza94]

it works but if I use a calendar to filter year the measure goes crazy. I've tried to solve the problem.

Can you help me again please? 😢

https://drive.google.com/drive/folders/1396dUgbZcALj2d4ySvj1sYHbShYMTfrx?usp=sharing

[v-yetao1-msft]

Hi @giuliapiazza94 

Please follow the steps in the link .

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[giuliapiazza94]

Thank you so much

The file pbix:

https://drive.google.com/drive/folders/1396dUgbZcALj2d4ySvj1sYHbShYMTfrx?usp=sharing

In the example, Robert has a positive total delta sales ( = 25)

So, i set a filter "delta sales" > 0. But, I'd like to see all Robert's delta sales, the negative too. (B -17, D -10)

But, if I set "delta sales" > 0 the negative delta disappear, so I'd like to filter only total delta sales

[v-yetao1-msft]

Hi @giuliapiazza94 

I do not quite understand what you mean . Do you want to filter out data with total>=0? But if N3050 such > 0 data will not be filtered ? If so, can you provide your sample or pbix file(remove sensitive info) so that we can better deal with the problem . 

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[giuliapiazza94]

How can I attach a .pbix file?

[SpartaBI]

@giuliapiazza94 I don't know your column names but let's say you have there 'Table'[Customer] and 'Table[N] columns.
Write this measure:
CALCULATE(SUM('Table'[delta sales]), REMOVEFILTERS('Table[N]).

Put that measure on the visual filter pane and make it to be greater than 0.
This will keep all the relevant customers with all their data.

      

Showcase Report – Contoso By SpartaBI