Suppress pivot table row if expression is zero

[sifatnabil]

Hi all,

I have a pivot table with 2 dimensions, Customer and Product Group, and I only want to show rows where sum(Sales)>0 for both ProductA and ProductB. So in the below example, only the highlighted rows should be shown because there are non-zero values in both products. How can I suppress the others?

Customer	Product Group	ProductA	ProductB
A		84	568
B		456	-
X		-	76
D		-	567
G		34	4
R		-	3
S		-	567
I		-	567
L		10,999	2,003,111
P		644,929	854,950

[YoussefBelloum]

Hi,

try this:

Sum( {<Customer={"=sum(Field)>0"}>}  Field)

[sifatnabil]

Hi, this still shows all the rows.

[YoussefBelloum]

did you replace Field with your Measure field name ?

Can you attach some rows of your data or sample data ?

[sifatnabil]

Yes I did. Here's the raw data:

Customer	Product Group	Sales
A	ProductA	84
B	ProductA	456
X	ProductA	-
D	ProductA	-
G	ProductA	34
R	ProductA	-
S	ProductA	-
I	ProductA	-
L	ProductA	10,999
P	ProductA	644,929
A	ProductB	568
B	ProductB	-
X	ProductB	76
D	ProductB	567
G	ProductB	4
R	ProductB	3
S	ProductB	567
I	ProductB	567
L	ProductB	2,003,111
P	ProductB	854,950

[MK_QSL]

Use calculated dimension for Product

Dimension

1)

=Aggr(IF(Aggr(SUM(Sales),Customer) <> Aggr(SUM(Sales),Customer,[Product Group]),Customer),Customer,[Product Group])

Tick Suppress When Value is Null

2) [Product Group]

Expression

SUM(Sales)

NOTE : This will work only if there are only two Products in Product Group.

[MK_QSL · Accepted solution]

You can use below.. This should work for any number of Product in Product Group.

Dimension

Customer

Product Group

Expression

IF(SUM(TOTAL <Customer> Aggr(IF(SUM(Sales)>0,1,0),Customer,[Product Group])) = Count(Total Distinct [Product Group]),SUM(Sales))

[YoussefBelloum]

excellent