Home Forums Power Pivot Excel DAX measures moving to other columns after reopening

This topic contains 6 replies, has 2 voices, and was last updated by  sjhc1177 4 weeks ago.

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • #18187

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 53

    Hi, strange thing. I’ve had this happen several times, when opening Excel all of my measures (150) that were sorted to be in a specific order for ease of use.

    When I open powerpivot I see them all just dumped into a random order. Rather than resorting them again and again. Is there a reason why this keeps happening. And is there a quick way to move them all to a specific column.

     

    It’s driving me nuts.

     

    Thanksw

    #18188

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    If you are talking about measure order in the pivot table field list, have you tried selecting the option to Sort A to Z under the field list settings icon (near upper right corner of Pivot Table Fields dialog on my machine, probably the same location on yours)?

    Again, if you are talking about measure order in the pivot table fields list, what some people do is create tables with no rows just to group like measures. For example, there might be a table in the Power Pivot window called ‘Product Measures’, another table called ‘Customer Measures’, another table called ‘Sales Measures’, and so on. After creating these tables, they just use the Manage Measures dialog to change the table a measure is associated with (see the Measures dropdown in the Calculations group on the Power Pivot ribbon). Combined with the option to sort measures A to Z, you should get something quite workable for you.

     

    #18189

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 53

    Hi Tom, Thanks for responding.

    In power pivot i have dummy columns created. Under the measures section I’ve created 150 or so measures. They were sorted under each column IE This Year, Last Year, 2 Years Ago. And the associated measures were under each column. Then when I opened Excel today I see everything is under the first few columns. Not nested under the 10 or so columns created.

     

    I would love to find a way to resort them.

    Not have to cut and paste the measures under the correct column.

     

    Very strange.

    Attachments:
    You must be logged in to view attached files.
    #18191

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    Steven,

    That is interesting.

    When it comes to ordering, sometimes the grid area below a table where measures can be entered has a mind of its own.

    Instead of creating dummy columns, you might consider creating dummy tables and move measures as appropriate. It is a relatively common practice (not everyone follows it, but quite a few do). One person I know starts the dummy table names with an underscore (e.g., _MyMeasureTableName), allowing the measure tables to group together in a pivot’s field list.

     

    #18192

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 53

    Ok, so I’m not alone with this.

    To be clear, I’ve created a dummy table in excel. Brought it into powerpivot and then under the columns created the measures. That have just moved to the first 3 columns in the pivot table. Rather than being neatly placed under many columns.

     

    Another question I have is when I move pivot tables to order them. When I reopen the file they are back in the same order. Which i think is the order they were added to power pivot. Am I correct in assuming that?

     

    Thanks

    #18193

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    When I work on a project with many measures, I use “dummy” tables with appropriate names that are each associated with a specific category of measures.

    Not sure I understand the other question. Are you saying that a pivot table on an Excel worksheet that you have moved to a different location on the same sheet (or to an altogether different sheet) reverts back to its original location once you have reopened the workbook? Or are you describing something in the Power Pivot* window?

    _* I use the term Power Pivot window to mean what you see after you click the “Manage” button of the Data Model group in the Power Pivot ribbon.

    #18194

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 53

    Hi Tom, Sorry for not being clear.

    Yes I’m talking about the window that comes up when you click MANAGE.

    My tables wont stay in the order i put them in. Then within the tables my measures from time to time clump all in the first few columns of the table.

     

    Steven

Viewing 7 posts - 1 through 7 (of 7 total)

The forum ‘Power Pivot’ is closed to new topics and replies.