alternate solution to pad with 0 when there is no data.
check sheet validate1,
the calculation isQuarterSubTot returns true for the total cells.
first() = last() computing Year,Quarter,Month restarting Quarter
the index() function on the detail shelf allows to have a label on every mark.
You can see on sheet Validate2 , if you remove it from the Text shelf you lost the True on the Group E Q4 total.
On final view sheet, with index still on the detail, you can compute the values with
if [isQuarterSubTot] then ifnull(SUM( [Value] ) /3,0)
ZN Padding(mc).twbx 33.1 KB
Thanks for the reply MADHURI.TANNIR! This works, until I put a filter on the date (see attached), if the filter excludes a month, in the example, Oct 2017, the Get the Avg calc is still dividing by 3, which throws off the sub total column. I tried to use size() to help this, but it seems like the size() is doesn't change from each partition per subtotal (stays 2 for Q1 of 2018)
Again, all help is greatly appreciated!
You can go with
if [isQuarterSubTot] then ifnull(SUM( [Value] ) /[Nb month in view],0)
Where Nb month in view is
WINDOW_MAX( [Nb month in view per group] )
and Nb month in view per group is
pay attention to the computing of all calculations in the final nested calculation
ZN Padding(mc2).twbx 38.8 KB
Thanks for the info! That is some impressive nested table calcs there, I have been trying to replicated the results on a new tab in the same work book that you attached, and can't quite get it to match yours. I will tinker around with the computing options for the final calc, and see if I can match your results.
Thanks again for the help!