4 Replies Latest reply on Dec 13, 2016 7:56 PM by koushik.neelakantha

# Need to Hide Row Level Values

Hello everyone,

Please help me out in this aspect. I have a hierarchy with Account, Buying Behaviour, Speciality & Procedure.

I have a sales Measures.

When i take hierarchy and go with sub total. My subtotal value is correct but my individual row level values are incorrect.

I just want to hide row level values and just show sub total & grand total.

But if single procedure name is there under one speciality, then it will show up the Amount in the corresponding single row too.

I need to show only the sub-total values for Amount not for each row.

Regards,

Koushik.

• ###### 1. Re: Need to Hide Row Level Values

Koushik,

Check in the attached...

You can see on the sheet Validate Data, I used a bunch of calculations using the first() and last() function , returning different values depending if it is a row value or a Total value.

The fact that some Speciality may have a single Procedure Name added some complexity.

Based on the returning values of those calculations , we can change the SUM(Last 12 Months....)  with the following.

This is a nested calculated field and you must apply different computing on each calculation.

So on the Final sheet, if you right -click  on the green pill and go in Edit Table calculation, check the computing for every Calculated Field.

if SUM( [Count Procedure Name by Speciality] ) = 1 then

if ([last computing on Speciality] = 0 and [first computing on Speciality] = 0)

or

([last computing on Speciality] = 0 and [first computing on Speciality] != 0)

then null

else SUM( [Last 12 Months Sales-Roll Sales] )

end

else

if [first = last  computing on Procedure Name] then SUM( [Last 12 Months Sales-Roll Sales] ) else null end

end

Michel

1 of 1 people found this helpful
• ###### 2. Re: Need to Hide Row Level Values

Hello Caissie,

Thanks a ton for the reply. I have few queries. Please help me out.

1) When i drill up to speciality level , I am unable to view the data. my requirement should be when i drill up & down only subtotals should get appear.

2) After selecting compute using as Pane down, I am able to drill up & down, but still Value is getting visible for speciality(Bariatric).

Regards,

Koushik.

• ###### 3. Re: Need to Hide Row Level Values

If you need to drill up&down this solution wont work.

You can have a solution allowing to drill up only at the Speciality level, by replacing the calculation part computing on Procedure Name by  if ATTR(Procedure Name) =  ATTR(Procedure Name).

(see sheet  Final - Drill up Procedure Name)

Or you could have a solution with full drill up/down  only by using the condition if if ATTR(Procedure Name) =  ATTR(Procedure Name). The only problem here is that if a Speciality have a single Procedure Name it wont display the Total for that Speciality. If in your real data, you dont have Specialities with a unique Procedure Name, you can go with this, otherwise I don't see how to accomplish your requirements with full drill up/down.

(see sheet  Final - Full Drill up - bug if single Proc Name)

1 of 1 people found this helpful
• ###### 4. Re: Need to Hide Row Level Values

Thanks a lot Caissie.

I will try to implement sheet Final with Pane down option.

Regards,

Koushik