Pivot Table not counting Zeros

[rickyilas]

Hi There!

I have a database with monthly calculations. Inside are with value and some are zeros. I need to count only those that has values >0 in the Pivot table but it is counting also the zeros because it has the formula in it.

Is there anyway to count in Pivot not including the zero?

Attached is the excel table.

Thanks for your help
Ricky

[dosydos]

I am not sure if you really can.
You could do something like what i attached.
Utilize a Unique function and then a filter function.

=UNIQUE(Table1[Project],FALSE)
=COUNT(FILTER(Table1[May-22],($A16=Table1[[Project]:[Project]])*(Table1[May-22]<>0)))


Its not great but it could work. Transformed your data into a table as well.

[rickyilas]

Thanks Dosydos for your reply. Appreciate it.

But i would prefer not using any formula and just getting the result in the pivot table itself.
I found some instructions in the net but couldn't do it. I don't know if i am missing something in the instructions:

Drag the first column to the row labels.
Drag the second column to the Data Area twice.
Right click on one of the data area columns and under Summarise data by, select Count.
Click on the filter drop down under row labels and under Value Filters, select Greater than.
Enter 0 there.
Click on OK.
(I don't understand the column he means.)

[Pepe Le Mokko]

This is due to the fact of your formatting of cells as they now contain an hyphen.
If you remove this formatting to make them blank cells, your count is correct

[XOR LX]

Hi,

So you're going to add each and every month to the Values area of the Pivot Table? Doesn't sound very flexible, nor the most pragmatic approach.

I would prefer to have the months in the Columns area, which I would do by first unpivoting those columns using Power Query. I would then add the resulting table to the Data Model, so that a measure can be created to give you your desired totals.

See the attached.

Regards

[rickyilas]

Thanks Pepe for your reply. I tried making a formula in my original data that if it is 0, it should be "" blank, but even this ""blank is being counted. The attached file has no formula but just hypen resulted from copying as values.

[rickyilas]

Hello Xor,

Thanks for your file and it is exactly the result I wanted. Unfortunately, I don't know how to use power Query and don't understand how you did the pivot. I was looking for the Data source but couldn't find it, maybe because it is Power Query. i would have loved to learned how to do that. Any reference?

Thanks

[XOR LX]

You can look at the transformations I did in Power Query via the Data ribbon (Queries & Connections) and then double-clicking on the query in the pane on the right.

That transformed table is then used as the source for the Power Pivot table, which you can see by opening up the Data Model (Data ribbon/Manage Data Model).

Regards

[Pepe Le Mokko]

Thanks Pepe for your reply. I tried making a formula in my original data that if it is 0, it should be "" blank, but even this ""blank is being counted. The attached file has no formula but just hypen resulted from copying as values.

As I suggested, you need to use BLANK cells; The "" is a text string ( although it is an empty one) which then will be counted in the PT
Of course if you copy/pasted from somewhere else it's a different story

[rickyilas]

Thanks all for your reply. I will try to explore Power Query, i think it's the solution I can use to achieve this problem.

Cheers!