Correct Subtotals and Grandtotals

[azbasketcat]

I'm working on a pivot table where I want to move certain expenses from a department in one category to a department in another category. My pivot table is set-up with the "Category" and "Department" fields in Row Labels. My departments sum correctly in "Asset Retirements" based on the forumlas below, but all of my subtotals sum to zero. I recognize that I will not get the correct subtotal based on "Asset Retirements - All Stores", but I do not understand why it returns zero at the subtotal level.

I have read Rob Collie's book and his PowerPivotPro blog post on Subtotals and Grand Totals That Add Up “Correctly” « PowerPivotPro, but I'm still having problems. Any help would be appreciated. Thank you - Randy

Here are my formulas:

Asset Retirements =
IF(HASONEVALUE(PS_Depts[Dept]),
IF(VALUES(PS_Depts[Dept])=7000, [Asset Retirements - All Stores]+
[Asset Retirements - Store ID less than 4002],
IF(VALUES(PS_Depts[Dept])=9001, -[Asset Retirements - Store ID less than 4002],
[Asset Retirements - All Stores])
),
[Asset Retirements - All Stores])

Asset Retirements – All Stores =
- CALCULATE(sum(GLAMTS_SUMM[ACT_AMT]),Summ_Accts[SUMM_ACCT]=600)

Asset Retirements - Store ID less than 4002 =
- CALCULATE(sum(GLAMTS_SUMM[ACT_AMT]),Summ_Accts[SUMM_ACCT]=600,filter(all(PS_Depts),
PS_Depts[Dept] <= 4001))

 

[azbasketcat]

Just as a follow-up, I am able to get the following formula to work at the Subtotal and Grand Total levels, however the detail items obviously only show the constant "1". If I replace the "1" in the "True" component of the If Statement with a measure or formula (e.g. [Asset Retirements]), the measure works on all of the detail items (where PS_Depts[Dept] = 1), but the subtotals again return zero.

Asset Retirements - SumX = if(HASONEVALUE(PS_Depts[Dept]),1,SUMX(VALUES(PS_Depts[Dept]), [Asset Retirements]))

 

[powerpivotpro]

Given what you have said, it seems like your measure itself is returning 0 in those cases. If you try the following experiment, does it also return zeroes on subtotals in all cases?

if(HASONEVALUE(PS_Depts[Dept]),[Place Lots of Different Measures Here and See What You Get],SUMX(VALUES(PS_Depts[Dept]), [Asset Retirements]))

 

[powerpivotpro]

Upon further reading the whole thing just seems incredibly unlikely. Changing what is specified in the TRUE branch of an IF should never have any impact on what happens in the FALSE branch of the IF.

 

[azbasketcat]

Thanks Rob for taking time to look at it. I feel like it's an honor to get your input, as I have been looking at your book and blog all day.

I agree that changing something in the True branch of the IF Statement, shouldn't have an impact on the False branch, but for whatever reason it is having the impact that I stated above.

I rebuilt the Measures in a new workbook, with a smaller subset of data and the subtotals work using SUMX. I'm going to play with the real workbook this weekend and see if I can get it to work, otherwise I think there may be some kind of glitch and I'll need to rebuild the workbook from start.

Thanks again for your help. As a plug to you, I think your book is one of the most readable and effective IT books I have ever read. The book, your blog and Mr. Excel have all been a great help in starting to learn PowerPivot. Randy

 

[azbasketcat]

I rebuilt the measures in the original workbook (based on the success I had in the smaller Test Workbook) and I'm still getting the same problem. Zero for subtotals, when I have a measure in the True part of the If statement and the correct subtotals if I put the number 1 in the True part of the If statement.

Not sure what to do, I have about 90 measures in the workbook and don't really want to rebuild from scratch. I am running 32-bit Excel 2010 and PowerPivot v2. I tried clearing out the VertiPaq temp files and added a calculated column in one of my tables that I deleted in an attempt to refresh the workbook, but nothing has helped.

 

[powerpivotpro]

Is the workbook too sensitive to share?

 

[azbasketcat]

Is the workbook too sensitive to share?

Unfortunately, it's the Profit and Loss data for a private company. However, I created a new pivot table reusing the same measure and it works perfectly in the new pivot table.

Have you seen where a PowerPivot table can get too big or complex and not work correctly?

 

[azbasketcat]

I figured out the problem. If I filter out some of the Departments using the filter in the "Department" Row Labels dialog box, the results of the SumX function return zero for the Category subtotals.

If I clear the filter, the subtotals are correct. Not sure I completely understand why, but I can filter out that data when I bring the data into PowerPivot as opposed to using the filters in the pivot table.