locked
PowerPivot V2 DrillThrough(Show Detail) amounts as text RRS feed

  • Question

  • I use PowerPivot V2 for Excel 2010 in Windows 7. I create a Pivot Table from a simple Excel table by using PowerPivot. When I double-click any given subtotal or grand total on a Pivot Table, the new sheet is inserted to show the detail of subtotal/grand total.  (Drill-through/Drill-down/Show Detail function).  It is working and the list of detail is correct.

    However, the cell format of amounts is text format.  I can not change it to number format no matter how I tried.  I change the cell format to number on the inserted sheet, I change the cell format of original database Excel table to number before importing PowerPivot...none of those works.  No matter how I try, the drill-through numbers stay as text.  

    As a good practice, If you drill-through any given subtotal/grand total, you would like to highlight the amount cells to see the detail total equals to the sub/grand total.  If the detail numbers are in text format, I can not visually verify if detail total really equals to the original sub/grand total.  When I highlight detail amount cells, all I see is the cell count.  But Cell count can not verify total of detailed cells of amounts.

    Do you have any solution to this?  Did I do something wrong?

    Thank you very much.


    LiLi803

    Friday, June 28, 2013 1:22 AM

Answers

  • I don't think there is an easy solution for this like you might think.

    One way I work around this, however, is to quickly convert the entire column to a Number format.

    Try this after executing a Drillthrough:

    1. Select the column header for the column you would like to sum (so that the entire column is selected)
    2. Click the Data tab and click 'Text to Columns' in the "Data Tools "section
    3. Accept the defaults ("Delimited", "Tab", "General") and then click "Finish"
    4. Your column should now be formatted as numbers and leave you with the ability to see a column sum in the bottom right of your worksheet (assuming your column is still highlighted) or to use a SUM function at the bottom of the column

    David


    David Duncan

    Friday, August 23, 2013 1:48 PM

All replies

  • I don't think there is an easy solution for this like you might think.

    One way I work around this, however, is to quickly convert the entire column to a Number format.

    Try this after executing a Drillthrough:

    1. Select the column header for the column you would like to sum (so that the entire column is selected)
    2. Click the Data tab and click 'Text to Columns' in the "Data Tools "section
    3. Accept the defaults ("Delimited", "Tab", "General") and then click "Finish"
    4. Your column should now be formatted as numbers and leave you with the ability to see a column sum in the bottom right of your worksheet (assuming your column is still highlighted) or to use a SUM function at the bottom of the column

    David


    David Duncan

    Friday, August 23, 2013 1:48 PM
  • I don't think there is an easy solution for this like you might think.

    One way I work around this, however, is to quickly convert the entire column to a Number format.

    Try this after executing a Drillthrough:

    1. Select the column header for the column you would like to sum (so that the entire column is selected)
    2. Click the Data tab and click 'Text to Columns' in the "Data Tools "section
    3. Accept the defaults ("Delimited", "Tab", "General") and then click "Finish"
    4. Your column should now be formatted as numbers and leave you with the ability to see a column sum in the bottom right of your worksheet (assuming your column is still highlighted) or to use a SUM function at the bottom of the column

    David


    David Duncan

    David:

    Thank you very much for your reply.  I am so sorry that I have not replied to your comment sooner because I failed to see the alert e-mail which was reclassified to Non-In-Box folder.

    Anyway, it seems the only solution is to Delimited it.  I know it can technically work but I am so unhappy about Microsoft's idea of deciding to present numbers(I'd say "Amount" because 99% of time is it) as TEXT.  What kind of mind allow numbers as text...I am sorry I have no idea.  The users who would use PowerPivot should be extremely "Number-oriented".  I think those people who would like to dare to use PowerPivot is dicing, slicing, subtotaling, re-organizing numbers all day long.  "Drill-Down" is a part of our daily operation because again, it all about "Numbers".  Then it is extremely frustrating to have the "TEXT" numbers.  I wish this would change in the future, but I guess I have no luck with having PowerPivot for Excel 2013 because there' no free add-on PowerPivot for Excel 2013...

    Yes, I truly appreciate PowerPivot to the extent that I just can not comprehend that this type of almost perfect Add-on has such a gross flaw....

    Thank  you very much for your input.  Again, I am sorry I have not even replied to your comment.

     



    LiLi803

    Friday, August 30, 2013 6:33 AM
  • Another option:

    1. Enter the value "1" in an empty cell

    2. Select the cell with "1"in it and copy

    3. Select the cells you want to convert to numeric values

    4. Right click in the selection

    5. Choose "Paste Special>Multiply"from the right click menu

    6. Format the selection to suit your needs.

    This method has the advantage of allowing you to process multiple columns in a single operation. It works for date values as well.

    A side note to Microsoft: I'm sure someone in your organization can give some great reasons why they've incorporated such undesirable behavior into your primary BI interface. As a user who has to spend extra time and effort on every PowerPivot analysis because of the counterproductive behavior built into your product, let me assure you that those reasons don't mean squat. Your competitors products are capable of preserving data formats from the data source, through the data model, into the presentation layer, and into the drill down, which is why I turn to them at every possible opportunity.


    George W. (different George W.)

    Monday, February 3, 2014 3:42 PM
  • George W:

    Thank you very much.  I am sorry I have not reply sooner.

    Yes, the solution would work...but I would slicing/dicing numbers from all directions to analyze a huge database to evaluate some variances.  I drill-down so many numbers so many times during my analysis...I just wish I can just get a subtotal by just highlighting rows(spending 1 second), them move on to the next analysis...

    The bottom line, I know this is something I can never change.  By default, drill-down numbers are shown as text.  If you use normal pivot table, drill-down numbers are not text but drill-down doesn't work if grand total is generated by slicer(Report Filter works).  That is the reason I get PowerPivot.  Yes, PowerPivot correctly drill-down itemized data even it is narrowed down by Slicer. BUT numbers are TEXT.  One way or another.

    I wonder how drill-down of grand total by Slicer in PowerPivot 2013 are presented.    Number or Text?  I can't tell because I don't have PowerPivot 2013.

    Thank you very much for your insight.  


    LiLi803

    Friday, March 14, 2014 1:37 AM