Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Set Analysis to include zero value

Hi,

I have a situation here where I want to show the zero values on sales data.

Sample data:

DateBranchSales
05/03/2015US0
06/03/2015US2000
07/03/2015US2500
05/03/2015Germany5000
06/03/2015Germany1000
07/03/2015Germany0

I have created a pivot table, with Date and Branch as dimensions, and Total Sales as expression. I have used set analysis for expression. For instance, Sum({\$<[Branch]={"US"}>} [Sales]).

If I Don't "checked" the suppress-zero option, the result pivot table will be something like below:

DateBranchTotal Sales
05/03/2015US0
06/03/2015US2000
07/03/2015US2500
05/03/2015Germany0
06/03/2015Germany0
07/03/2015Germany0

This is not the desired result because the desired result should only show all US branch's Total Sales.

So, If i "Checked" the suppress zero option, the zero dollar sales will totally be omitted. For instance:

DateBranchTotal Sales
06/03/2015US2000
07/03/2015US2500

Branch Germany did not show up in the table. However, for date 05/03/2015, the zero total sales was omitted.

The desired table i want to achieve as below:

Table A:

DateBranchTotal Sales
05/03/2015US0
06/03/2015US2000
07/03/2015US2500

Table B:

DateBranchTotal Sales
05/03/2015Germany5000
06/03/2015Germany1000
07/03/2015Germany0

Can someone please guide me on how to achieve this?

1 Solution

Accepted Solutions
MVP

Sample attached:

5 Replies
Anonymous
Not applicable

Hi ,

Hi

Uncheck the 'Supress Zero-Values' in preesnation tab

For TableA:

Sum({\$<[Branch]={"US"}>} [Sales])+sum( {1-\$} Sales )

For Table B:

Sum({\$<[Branch]={"Germany"}>} [Sales])+sum( {1-\$} Sales )

Note :

sum( {1-\$} Sales  :returns sales for everything excluded by the current selection

MVP

Create a Straight/Pivot Table

Dimension

Date

Branch

Expression

SUM({<Branch = {'US'}>}Sales)

Presentation

Untick Suppress Zero Values

For Germany,

SUM({<Branch = {'Germany'}>}Sales)

MVP

You can try with a calculated dimension for Branch.

If(Branch = 'US', Branch)

and then select 'Suppress When Value Is Null' for the Branch dimension on the dimension tab

MVP

Sample attached:

Contributor III
Author

Thank you Sunny, it works for me.

Community Browser