value filter does not work or is ugly

[S@S]

Hey all, i recently started using pivot tables in excel 365

in my data set i got some lines with 0 values. i want to filter out those values from my pivot so count for example does not include them.

i tried setting value filter on the needed field > 0 but nothing.

i tried setting value filter on the needed field > 0 after adding the field in the rows of the pivot and it seems to work.

the problem is the solution looks ugly as i display unnecessary values in my pivot this way!

i cant imagine there is no way to filter without displaying the values??

any ideas?

thx!

[cubangt]

Here is a good example on how to
https://www.extendoffice.com/documen...ro-values.html

[S@S]

Hi, thx for the reply!
the first soltion there > Hide Zero Value Row By Creating Filter Field is not an option as my data set will include new values every day. i cant go and include them all the time (!)
the second solution is what i tried initially but does not work if i dont include the field in my rows or works if i add it but it is ugly as i explained...

[romperstomper]

It would probably help if you posted a workbook. If you have rows with 0 values, there should be a field you can apply a value filter to.

[S@S]

yup, just did!

so what i need is the result of pivot 2 count for amount for vvv for Feb = 1 but without the yellow (amount) lines!

and yes i know i can collapse them but meeh, can it be without them there like pivot 1?

[romperstomper]

You can use Powerpivot with a simple measure of:

=COUNTROWS(FILTER(Table1,Table1[Amount]>0))

as in the attached.

[Richard Buttrey]

Add a Slicer which will give you two options, 1 or 0 then just pick the 1.

I suspect however that this is a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

[S@S]

ok never worked with powerpivot before... it actually looks very usefull as it can combine multiple data sources and make derived datasets out of them!

i think i can understand what =COUNTROWS(FILTER(Table1,Table1[Amount]>0)) formula does

apparently power pivot goes one step further and includes any of my pivot dimentions like Type and Date in our example to the result of the formula.

and yes this works as desired!

need to dig further on this, thx!

[S@S]

yea 1 and 0 where just dummy values. in essence i have blank values, 0 and >0 in my real application.
blank probably means the user forgot to enter the value, 0 means they know what they doing and say 0 revenue, while >0 is the 98% of the cases.

so with the COUNTROWS(FILTER(Table1,Table1[Amount]>0)) i think i cover all cases.

thx for the replies!