• Hide Calculated Items With Zero Totals In PowerPivot PivotTables

    The method for hiding items with zero totals in a PivotTable is different if you're working with a regular PivotTable or a PowerPivot PivotTable. This article focusses on how to accomplish this goal in the PowerPivot version. (If you're working with a regular and you want to hide calculated items that have zero balances, you'll want to check out Debra Dalgleish's blog post on the subject.)

    To start, assume that we’ve got a fairly simple PowerPivot pivot table that looks like this:

    Actual, Budget and Variance are all calculated fields (measures) which are made up as follows:
    [Actual]: =-SUM(tblTransactions[Amount])
    [Budget]: =-SUM(tblBudget[Amount])
    [Variance]: =[Actual]-[Budget]

    Notice that we’ve got some blank cells in the Actual column, a bunch of zeros in the Budget column and a bunch of zeros in the Variance column. This would indicate that:

    • There were no transactions at all for the blank cells in the Actual column,
    • There was a budget amount of $0 held in the Budget table, and
    • The Variance column of BLANK – 0 = 0.

    So at this point, we’re tempted to try the trick that Debra blogged about to hide those zeros. But if we right click “Green Fees – 18 Junior Wk-Day”, select Filter --> Value Filters, we get the following:

    This isn’t good, as we're being forced to choose one of our measures. We can’t choose Budget, as there are amounts where budget is 0 but there were revenues. We also can’t choose Variance, as it’s possible that Actual and Budget could match even if they weren’t 0. And we certainly can’t choose Actual either. So now what?

    The secret to this is that PowerPivot automatically suppresses lines that are blank. So we need to modify our measures a bit:
    [Actual]: =IF(SUM(tblTransactions[Amount])=0,BLANK(),-SUM(tblTransactions[Amount]))
    [Budget]: =IF(SUM(tblBudget[Amount])=0,BLANK(),-SUM(tblBudget[Amount]))
    As you can see, we use an IF function to test if the value of the line item is 0. If so, then we put in a BLANK, and if not we return the result.

    The resulting PivotTable now suppresses any lines where the Actual column was blank (or 0). In addition, all 0’s in the Budget table are converted to blanks as well. And the end result is that those rows are filtered out of our table, as you can see below:

    And that’s all good, but now we have blank cells in our PivotTable. I personally can’t stand that, as it always looks unfinished to me. No worry though, as we can just flip the PivotTable options to show blank cells as 0.

    Right click the PivotTable, choose Options, make sure “For empty cells show:” is checked, and enter 0. Click OK and you’re done!

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

  • MVP Logo
  • Recent Forum Posts

    p45cal

    Please help!

    Aagh. Beaten to it!
    Power Query solution at cell H10.
    Update the data in the table at cell A1 then right-click the result table and choose...

    p45cal Today, 11:27 AM Go to last post
    alansidman

    Please help!

    A Power Query solution:

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Unpivoted Columns"
    ...

    alansidman Today, 10:39 AM Go to last post
    cprop76

    Thanks, sample attached!

    Thanks, sorry for the VERY late reply, I've been out of the office all week. I've attached a sample which hopefully makes sense! The left hand side is...

    cprop76 Today, 03:41 AM Go to last post
    p45cal

    Check cell format is Date ignore blank cells

    try:

    Click on the picture to get a clearer view.

    Oops, fotrgot the code:

    Code:
    Private Sub DateFormat()
    Dim myRange As Range
    ...

    p45cal Yesterday, 03:01 PM Go to last post
    RET

    Check cell format is Date ignore blank cells

    Try this

    HTML Code:
    Private Sub DateFormat()
       Dim myRange As Range
       Dim Celda As Range
       
       Set myRange = Range("B2:B14")
    ...

    RET 2020-11-29, 09:24 PM Go to last post