Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

My users have a hand built report showing tickets by time frame and their priorities.

Priority            Count
1                   68
2                   41
3                   61
N/a                 24
% of Priority 1     35%

The final row of their table is just the percentage of tickets that were a #1 for that week =A2/SUM(A2:A5) and it displays a rolling 52 weeks of data so a wide, ugly and useless report but I'm not in a position to influence that, only to improve the speed of delivery.

In my reworking of the report, I chose to use PowerPivot as it simplifies the data refresh scenario for us. I pulled the data back and everything looks good but I can't for the life of me figure out how to have it compute the above as a "grand total" row. If it's possible, what's the formula/expression/DAX thing I need to look at?

I know I can do the calculation "outside" of the pivot table but if they add a new priority or something of that nature, I think the refresh will bleed into the cell.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Your assumption about new categories bleeding into the cells in correct.

Provided your dataset has a third column of data you can use a Calculated Item to solve your problem.

You can achieve this by:

Click your Row labels in the pivot table... then click "options -> fields,items and sets". From there click calculated item.

In the name box Type "% of Priority 1" In the formula box type "='1' / ('1' +'2' +'3' +'N/a')"

Now your calculation in 'built in' to the pivot table.... there are some complications that arise from this solution.

Firstly your grand total row will now include your calculated item in it.You can get around this by creating another calculated it and simply adding up each of the fields except your % field. Another minor issue is that the formatting for your % column will try to match the remainder of the pviot table, so you will have to place the formatting "on top" of the pivot table, and this may cause the formatting to be lost on update occasionally.

Great question (I had not used that feature before... very handy).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.