May 30 2021 09:11 AM
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
May 30 2021 10:34 AM
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:
May 30 2021 10:43 AM
hi Mathes, thank you for the response. To make it simple, can you drag the decription field to the column? You see that it sums and it is text. Do you have a crafty way of being able to display a text field in the column but simply display (no summing).
thank you.
May 30 2021 11:10 AM
May 30 2021 11:10 AM
May 31 2021 01:23 AM
SolutionNot 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:
May 31 2021 04:15 AM
It very depends on what we would like to show for grouped rows (e.g. for entire month, quarter or year).
If we don't group or only first, when something like
Description_:=FIRSTNONBLANK(Table1[Description], "")
could work. If all descriptions in period, when iteration with CONCATENATEX().
Yes, in any case some measure.
May 31 2021 05:22 AM
May 31 2021 01:23 AM
SolutionNot 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: