Pivot Table: Filtering to include only values greater than 0 (but not by using row and column filters!)

[Optimesh]

Hello all,
I have a set of data, on which I run a pivot table. Each row in the data set describe a sale made by a salesperson. In each row we have:
salesperson, month of sale, profit on sale, transaction ID

The pivot shows the salespeople on the row labels and months on column labels.
The values shown are a count of the number of deals, performed by counting the transaction IDs
However, I want it to count only deals that have a profit higher than 0. Some of the rows show a profit = 0 and others a positive number.

How do I do that? I can't do it by using the row and column filtering... :[

One option is to add the profit variable to the Report Filter and uncheck "0", but I want a solution that will work even when I update the data set in the future. That is, if I enter more rows with profits in amounts that weren't there before, these won't be captured by the pivot table... :/

also, is there a way to do so without using VBAs or macros?

Thanks in advance for your help!

 

[p45cal]

You've not had an answer in 6 hours. One suggestion is to add a column in the source table and hava formula in it of the ilk:
=[@[ profit on sale]=]=>0
which will return true/false for profitable/non-profitable. Put this field in the Report Filter.

Is code which runs automatically on refreshing the Pivot table still out of the question?
If not, then something like this in the sheet's code-module:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Static Blocked
If Target.Name = "PivotTable2" Then
    If Not Blocked Then
        Blocked = True
        With Target.PivotFields(" profit on sale")
            .ClearAllFilters
            .PivotItems("0").Visible = False
        End With
        Blocked = False
    End If
End If
End Sub

 

[Optimesh]

I see.
Thanks for your help.
I wonder if this is a feature that's available in PowerPivot.

Thanks a lot!

You've not had an answer in 6 hours. One suggestion is to add a column in the source table and hava formula in it of the ilk:
=[@[ profit on sale]=]=>0
which will return true/false for profitable/non-profitable. Put this field in the Report Filter.

Is code which runs automatically on refreshing the Pivot table still out of the question?
If not, then something like this in the sheet's code-module:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Static Blocked
If Target.Name = "PivotTable2" Then
    If Not Blocked Then
        Blocked = True
        With Target.PivotFields(" profit on sale")
            .ClearAllFilters
            .PivotItems("0").Visible = False
        End With
        Blocked = False
    End If
End If
End Sub

 

[Fazza]

hi,

You can filter the data out of the dataset using SQL. It would be like below, adjust for your real field names,

SELECT *
FROM source_data
WHERE [profit on sale] > 0

It is easier (for me to explain) if you give your source data a normal defined name, not dynamic. From a separate workbook take the external data source option at the first step of the pivot table wizard ALT-D-P. Get Data, Excel files, OK, browse for the source data file, OK, see the source data, select something and follow the wizard to the end at which point you choose the option to edit in MS Query. Within MS Query apply the filter to exclude the less than or equal zero results then exit MS Query & complete the pivot table. The resultant worksheet can be moved into the source data file if you like.

If the source data will be changing, it might be better to have a source data based on the worksheet name. Syntax is [Your Sheet Name$]

So, add a $ to indicate a worksheet name. And enclose in brackets.

SELECT * FROM [your worksheet name$] WHERE [profit on sale] > 0

hth

 

[Optimesh]

Thank you for your help Fazza!

hi,

You can filter the data out of the dataset using SQL. It would be like below, adjust for your real field names,

SELECT *
FROM source_data
WHERE [profit on sale] > 0

It is easier (for me to explain) if you give your source data a normal defined name, not dynamic. From a separate workbook take the external data source option at the first step of the pivot table wizard ALT-D-P. Get Data, Excel files, OK, browse for the source data file, OK, see the source data, select something and follow the wizard to the end at which point you choose the option to edit in MS Query. Within MS Query apply the filter to exclude the less than or equal zero results then exit MS Query & complete the pivot table. The resultant worksheet can be moved into the source data file if you like.

If the source data will be changing, it might be better to have a source data based on the worksheet name. Syntax is [Your Sheet Name$]

So, add a $ to indicate a worksheet name. And enclose in brackets.

SELECT * FROM [your worksheet name$] WHERE [profit on sale] > 0

hth

 

[Fazza]

You're welcome, Optimesh.

Note, the specific solution (I gave) filters the dataset so that the pivot table only has the data with [profit on sale] > 0

If you want to keep all the original data and still be able to distinguish [profit on sale] > 0 then add another field in the SQL.

regards