Hide subtotals in pivottable with only one row

[miguellagos]

It is possible to hide subtotals when there is only one row?

Example:

Month Product Value
----------------------------------------------
Jan xxx 10.00
yyy 3.80
zzz 2.00
Sub-Total Jan 15.80

Feb yyy 4.40
Sub-Total Feb 4.40

Mar xxx 6.25
zzz 4.00
Sub-Total Mar 10.25

I want it to look like this:

Month Product Value
----------------------------------------------
Jan xxx 10.00
yyy 3.80
zzz 2.00
Sub-Total Jan 15.80

Feb yyy 4.40

Mar xxx 6.25
zzz 4.00
Sub-Total Mar 10.25

Miguel Lagos

 

[ruve1k]

Create the following measure:

[Amount]

=IF(  COUNTROWS(VALUES(Table1[Product]))>1 
   || ISFILTERED(Table1[Product]), 
          SUM(Table1[Value]),
          BLANK()
  )

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Month</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Product</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Amount</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Jan</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">xxx</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">10</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Jan</td><td style="border-right: 1px solid black;border-left: 1px solid black;;">yyy</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">3.8</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Jan</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">zzz</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Jan Total</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">15.8</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Feb</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">yyy</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4.4</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mar</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">xxx</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">6.25</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mar</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">zzz</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mar Total</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10.2</td></tr></tbody></table>

Sheet1​

Note that other measures without this logic will cause the pivot table to show the unwanted subtotal row.