Tagged: dax; pivot table
September 21, 2019 at 2:13 pm #18187
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.
ThankswSeptember 21, 2019 at 4:09 pm #18188
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.September 21, 2019 at 5:19 pm #18189
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.
Attachments:You must be logged in to view attached files.September 21, 2019 at 5:38 pm #18191
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.September 21, 2019 at 5:44 pm #18192
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?
ThanksSeptember 21, 2019 at 6:01 pm #18193
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.September 21, 2019 at 9:58 pm #18194
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.
The forum ‘Power Pivot’ is closed to new topics and replies.