Forum Discussion
Excel Pivot - Move Row to Column (wants to sum the text)
Hello,
I have a pivot and I do not like the format of it.
I want to move the the row for "description" (the red starred in the pic below) from the row to the column but when I do this it wants to sum this text field. How can I move the row to the column but make it display the text as it is?
thank you
thank you
Not something you'll find on a Mac mathetes , I'm afraid.
You'll need the DAX formula to create a measure in the Data Model, that will allow you to drop a text in the value field of the pivot table. The end result then looks like this:
7 Replies
The Pivot Table, as you know, is a powerful and useful feature of Excel; many consider it one of the most useful capabilities. Its power comes in being to summarize data in a cross-tabulated way, summarizing where fields are in common by row and/or by column.
One of the difficulties with your data, I think--not sure since I'm not fully familiar with it--is that each row in your database is actually distinctive in at least one of the fields. So it's hard to even know what kind of summary you're expecting. Here, to illustrate, were a couple of possibilities I came up with.
In my own experience with Pivot Tables, I've often found it necessary to play around with the various combinations and possibilities before finding the format/layout that provides the summary I have in mind. You may have done a lot of that experimentation already......it might be that what you need to do is change the raw data itself, not to make it inaccurate.... but to make it more summarizable.
For example, you have the "Date of Amendment" field specified, AND you say under description, such things as "1st milestone extension, 2nd amendment of the LC," which strikes me not only as wordy but perhaps only helpful as a follow-up investigation into significance of any given summary number.
My questions are:
- are those two fields redundant in many or all of the cases?
- And if so, can one of them be eliminated altogether from your pivot table, so that the other can be the basis for a cross-tabulation?
- Or, for the purposes of this summary, assuming you keep the dates, does it matter which amendment is applicable to a given row?
- We know that it was amended, so who cares {for the purposes of this summary] which specific amendment.....this is?
- You can always click on a cell in a Pivot Table to have Excel highlight the detail rows that are summarized in it (Did you know that?)
- We know that it was amended, so who cares {for the purposes of this summary] which specific amendment.....this is?
- I did drag it there, and just saw that it made the set of columns ridiculously long....and useless. I didn't see what you seem to be describing. Can you post a picture of what you're describing and don't want? But also come up with some better description or manual creation of what you do want. I gather that the two images I sent back don't come any closer to the cross-tabulated result than what you already had. It would help if you gave feedback rather than just reiterating what your first post said.
- are those two fields redundant in many or all of the cases?
