# Show only non-zero balances in PowerPivot table



## BrucePorter (Aug 23, 2012)

Hi

I'm relatively new to PowerPivot but have been using Excel for a number of years. I currently have a trial balance which is imported into PowerPivot which is made up of several fields including accounting period, account code, several financial dimensions & a financial value. I also have a mapping table which I use to map these accounts & dimensions into positions in our Management Accounts format.

 At the moment the Pivot Table I've created has the following fields - Accounting Period, Positioning Description and Balance. However, the table shows lines which total to zero i.e. where there have been transactions during the period which are equal and opposite. I'd like to have some way of excluding these lines from the Pivot Table so that it only shows lines with a non-zero balances. Is this possible?

 Any help would be appreciated.

Thanks

 Bruce


----------



## ruve1k (Aug 23, 2012)

I will assume that [Balance] is your measure.
Create a new measure:
[Non-Zero Balance] =IF([Balance]=0, BLANK(), [Balance])
Remove [Balance] from the pivot-table and only use [Non-Zero Balance].


----------



## BrucePorter (Aug 23, 2012)

Thanks ruve1k works exactly as I wanted.

I didn't realise that you could use the BLANK() function to do this.

Really appreciate your help.

Bruce


----------

