Dimensionality on the third dimension

[#1]

Hi All,

I had a pivot table with two dimensions. I needed to add a third, which is only a description. I'm using dimensionality to determine the percentage of the line to the total for each of the two dimensions. This has been working fine until I added the third dimension. Now I can't get the proper calculation when I expand the chart beyond the second dimension. I've added what I thought would handle the third but can't get it working.

Please see the attached spreadsheet. It has the chart collapsed (working) and expanded (not working). The expression is in the spreadsheet.

Thanks,

Rich

[#2]

Try adding dimensionality() as it's own expression to see what's going on.

[#3]

Thanks for the lead CK.

I'm not sure why my first expression didn't work but I added dimensionality () as it's own expression and it did indeed show me what was going on. I simplified the expression and changed the expression for the third level to the same as the second level and it now gives the right answer. For the sake of others:

pick(dimensionality()
,Sum(TOTAL <[Book Name]> [Sales Amount])/ Sum (TOTAL [Sales Amount])
,Sum([Sales Amount])/Sum(TOTAL <[Book Name]> [Sales Amount])
,Sum([Sales Amount])/Sum(TOTAL <[Book Name]> [Sales Amount]))

Rich