Hiding values in pivot table, but showing grand total on top

[MEBG93]

Hello experts, 

Is it possible to hide certain dimensions (and consequent values) but keeping the grand total on the totals row in a Pivot Table?

Let me explain with a brief example:

this first image shows all dimensions

Next, I want to hide table 2 but maintain the grand total

Can I do this by associating USERID with the "table" dimension?

Thanks!!

[MayilVahanan]

Hi @MEBG93 

Try like below

If(RowNo()=0, Sum(Values), Sum({<Table={'table1'}>}Values))

Change the fieldname based on ur data model

[MEBG93]

thanks @MayilVahanan  but it doesn't work. It just sums Values where Table='Table1'.

Also I'm trying this on a pivot table, maybe you're trying on a normal table

[MayilVahanan]

Hi @MEBG93 

Can you provide the sample app to verify and expected output.

Fyip: screenshot is pivot table..

[MEBG93]

Sure @MayilVahanan i'll send you a sample app. My bad, actually the app has more dimensions and the expression has to be applied to 'Tier 3' (either hide or show a certain value, in this case, show 'Table 1', hide 'Table 2')

What I'm looking for is this highlighted result but in the first column:

Thanks!

[MayilVahanan · Accepted solution]

Hi @MEBG93 

You are using more than one dimension, that's the reason. Above expression not satisfy ur requirement. 
Please try like below

If(IsNull(Rowno(Total)), Sum(Aggr(Sum(Value), Country, [Tier 3])), Sum({<"Tier 3"={'Table 1'}>}Value))

[MEBG93 · Accepted solution]

Thanks for the replies! I also found a solution using dimensionality(), by defining each dimension to a particular expresion. So for instance, I did:

if(Dimensionality()=1 or Dimensionality()=2,Sum({1<"Tier 4"={'Total'}>}Value),
if(Dimensionality()=4 or Dimensionality()=3, Sum({$<"Tier 3"={'Table 3', 'Table 1'},"Tier 4"-={'Total'} >}Value)))

I hope someone find this helpful