How to show text into a pivot table

[michelv]

Hi there,

I'm a bit new to this and hopefully you can give me an answer. I'm trying to put survey results into a power pivot table and for the questions that relates to an answer it all goes perfect. But...if people have the choice to fill in text, how can I show this in a nice way in Power pivot?

One other question, can I limit the vertical slicer. For instance there are 20 question (1 til 20) and I only want to be able to choose from 10 till 14 to show in the slice, is this possible (without excluding it in the database file)

Thanks in advance

 

[dmulle]

I am trying to get your suggestion to work, but need more detail. I am a bit rusty with my excel. Where do you insert the formula below? I tried inserting a calculated field, this was the only place I could think of - I replaced Table in your formula with the table name/range and the Text Column with the field name by inserting from the list in the calculated field box. However this did not work. if you dont put the correct formula it will not accept and do not offer any clues as to what you might need to change.

Any suggestions? I am needing to return comments along side a value to explain the number, the vaule and the comments are seperate fields.

Thanks

To return a text value from your source tables as a measure, try the following measure formula pattern:

=IF(COUNTROWS(VALUES(Table[TextColumn])) > 1, BLANK(),
   VALUES(Table[TextColumn])
 )

And your other question: no there is no way to limit what shows up on the slicer without modifying your underlying table data, sorry.

 

[dmulle]

Here is a sample of what I am trying to pivot - top line is my column headers, detail is on second line. I need the Explanation to appear in the pivot next to the Variance
[TABLE="width: 1467"]
<tbody>[TR]
[TD]IS[/TD]
[TD]Line Item[/TD]
[TD]2012[/TD]
[TD]2011[/TD]
[TD]Variance[/TD]
[TD]% Chg[/TD]
[TD].[/TD]
[TD]Explanation[/TD]
[/TR]
</tbody><colgroup><col><col><col span="3"><col><col><col></colgroup>[/TABLE]
[TABLE="width: 1467"]
<tbody>[TR]
[TD]Other Income:[/TD]
[TD]Exchange (gain) or loss[/TD]
[TD="align: right"]145,594[/TD]
[TD="align: right"](2,536)[/TD]
[TD="align: right"]148,130[/TD]
[TD="align: right"](5841.0%)[/TD]
[TD][/TD]
[TD]This account represents currency revaluation effect[/TD]
[/TR]
</tbody><colgroup><col><col><col span="3"><col><col><col></colgroup>[/TABLE]