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)
([last computing on Speciality] = 0 and [first computing on Speciality] != 0)
else SUM( [Last 12 Months Sales-Roll Sales] )
if [first = last computing on Procedure Name] then SUM( [Last 12 Months Sales-Roll Sales] ) else null end
Hide Row Level ed mc.twbx 80.0 KB
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).
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)
Hide Row Level ed mc2.twbx 117.0 KB
Thanks a lot Caissie.
I will try to implement sheet Final with Pane down option.