Drill Down Amounts showing as Text

[Andrew_UK]

Hi All,

I've had this issue for a while and have been searching for a solution. All I've found is this article https://social.msdn.microsoft.com/F...amounts-as-text?forum=sqlkjpowerpivotforexcel

But, I recognise that is dated June 2013 so I wondered if anyone (perhaps on here?) had found a solution to this issue?

The pivot table in question for me is used by sales people who will never be technically sufficient enough (or patient enough) to go through the necessary steps to turn their data into the correct formats. I need them to be in the correct formats at the point they drill down and add the new sheet.

I am tempted (since this is the only logical reason anyone would add a new sheet to the document) to create a VBA event when new sheets are added.

[COLOR=#00008B][FONT=Consolas]Private[/FONT][/COLOR][COLOR=#2E3133][FONT=Consolas] [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#2E3133][FONT=Consolas] Workbook_NewSheet[/FONT][/COLOR][COLOR=#2E3133][FONT=Consolas]([/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]ByVal[/FONT][/COLOR][COLOR=#2E3133][FONT=Consolas] Sh [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]As[/FONT][/COLOR][COLOR=#2E3133][FONT=Consolas] [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]Object[/FONT][/COLOR][COLOR=#2E3133][FONT=Consolas])
Format Cells 
[/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]End[/FONT][/COLOR][COLOR=#2E3133][FONT=Consolas] [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]Sub[/FONT][/COLOR]

I've not tested this theory yet though - is there a better solution?

Andrew

 

[Matt Allington]

Drill through with power pivot is horrible. What ever problems you have had before are likely to be a lot worse. So sorry, no solution here. Depending what you are trying to do, maybe there is another so'ution other than drill through, but that depends on the scenario.

 

[Andrew_UK]

Does this problem get resolved with Excel 2016?

The scenario is that it is all part of a "Sales Pipeline" I built recently. Sales people need to be able to see their projection for the month in the pivot table I've built, but then if they want to see what is driving those figures they double click on their figures to pull back a list showing all Invoices/Orders/Quotes... Obviously these contain Dates, Currency and Numerical information; Expected Close by _____, Currency ______ and Probability of Close this month/next month.

The probabilities are really the big leap which powerpivot has been able to drive through. In order to work out the probabilities it looks at the expected close, the number of days left this month, the customers previous order history, the type of customer... and then it works out the probability. Over the last 10 months it has proven to be remarkably accurate with it's projection and it's cut down on the amount of time that our managers need to spend manually doing the same process. So it's been overwhelmingly successful.

But, from a user perspective each individual sales person needs to be able to access the data that sits behind their figures. Returning it in a text format just isn't popular.

 

[Andrew_UK]

Unfortunately the VBA didn't work

I suppose I could give each sales person a macro on their Personal XLSB which converts it. But then they would still need to drilldown and then click on the macro.

It's bizarre that Microsoft couldn't have just built this properly in the first place. Given how fantastic powerpivot is you would have thought they'd allow for the cell formats to be consistent when you query the data.

 

[Matt Allington]

No the problem is not fixed in 2016. I have used a different approach for something else I think might work for you. I wrote VBA at selects the row text of the current pivot table (eg column B of the current row may be the row text) and then using VBA I switch pages to another pivot table that is set up the way I want to see the detail. The pre setup work is to place the row value from the first pivot on report filters, and then set up the detail in the pivot. The You can use VBA to set the value of the filter in the pivot.

I did this mainly by recording e code and then tweaking it - it wasn't hard.

 

[Andrew_UK]

Hi Matt,

I'm not sure I could build a pivot table that shows everything that people would want to see in a universal format. For instance in some territories the sales person may want to be looking at all orders within their territory at once, whereas for other territories you may want to click on one of the key accounts and see everything that is in the pipeline for that customer.

The real advantage of drill down is that it lets you select based on whichever row and column you wish.

I'm thinking I might just develop a macro and stick it on their Personal XLSB, but thanks for your help Matt. In a different scenario I may well end up stealing your solution one day :D