• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filtering a pivot table

S

snocaps

New Member
I have a pivot table grouped by slicers. I want to exclude 0 amounts in the groups. However, I've found that when I use filters to exclude 0's, the rows with the 0's in them are hidden, so when I click on a different slicer, the row is still hidden and anything that would have been displayed on that line is hidden. For example, one of the groups is 15 rows long, and 4 rows have 0's. If I filter to not show balances of 0, then when I choose a different group from the slicer, those rows are still hidden, so the data that would have been displayed (non-zero balances) is hidden. Is there another way to not display 0 balances and not hide the rows? I can't change the data source as it comes from a SQL query & updates when the workbook is opened.
 
Depends on your data structure.

How is SQL query brought into workbook? Is it through MS Query data connection that's loaded to table? Or is it PowerQuery loaded to data model?

If latter, you can set up additional transformation steps within PowerQuery to suite your need.

Alternately if you just don't want to show "0" in a cell. Go to File->Options->Advanced. Find "Display options for this worksheet" and uncheck "Show a zero in cells that have zero value".
 
It's not power query, it's through MS query loaded to the table. The pivot table is only 2 columns ... fund & balance, so if the balance is 0 I want the entire row to not show.

You did mention power query. I'm not so familiar with it, but wonder if I couldn't use power query on the table created by MS query, then build the pivot table off that instead? Seems like a long way around, but perhaps worth the time.....
 
PowerQuery is basically the same thing as MS Query. But with additional controls via GUI.

You can also edit MS Query Command Text (which is basically SQL query script with some limitations) to exclude lines with 0 values.
 
The lines themselves don't have 0 values, but the sum of the lines based on the fund may. That's what a pivot table is for, correct? To sum values based on criteria?
 
Not sure I understand you completely.

PivotTable is meant to reorganize flat table into more human readable cross-tab structure, summarizing values based on column and row headers.

If you can upload sample with raw data along with desired output. That'd help in providing you with best fit solution.
 
https://www.dropbox.com/s/q75mhz6xie7osyj/Cash_balances_SQL.xlsm?dl=0

Hopefully that worked. If you look at the pivot tab, I have filtered the balance column to not include 0's. If you click the "local & misc. funds" slicer, everything looks good. But if you click "affordable" it looks ok until you clear the filter -- then it becomes obvious there was a line missing. It's because it would have been on a line that is hidden because of the filter. I have included only a single month on the file I provided ... imagine what happens 6 months down the line ....
 
Ah, instead of applying filter at Balance column, apply it in Row Label field.

Right click on any Row Label, Filter->Value Filters
upload_2016-11-23_10-19-4.png

This will adjust as you change selection on Slicer.

FYI - CDBG Program (615) - 904 will still show as it's not actually zero. It's very small number (-2.273...E-13)
 
PERFECT! That's what I needed! I've never seen the value filters used on a row label field before.

Yeah, I know about that very small number ... I have a ticket in with the software vendor about the partial pennies issue. It's maddening.
 
You must log in or register to reply here.
Back
Top