Show unique values in pivot table

[#1]

I have a pivot table showing 'Ideas' at various Statuses (x-axis dimension) by Region (y-axis dimension).

However it is only showing one Idea per region and stage, when I know for some there are multiple Ideas.

My expression is very simple:

=Ideas.Idea.Title

As are my dimensions, which simple list the field name. The are not calculated in any way.

How can i show ALL the ideas at each Status and Region?

[jsingh71]

Tell me

"Ideas.Idea.Title" is your fieldname?

[morganaaron · Accepted solution]

That's the nature of showing dimensions in the way you are - unless there's a unique dimension value for each idea they'll be grouped and your expression will only show for region/stage combos that have only one potential value.

You could try use the concat() function which will give you all ideas for each combo, but this will format it inside of one cell, so if you have a lot of values it may not be a clear representation.

You'd need to add an ID dimension to return a unique value.

[#4]

Yes

[Digvijay_Singh]

You can try Concat(Idea field,',') as expression. If you have multiple values per region and status, it needs to be concatenated. Single field name in expression if results into multiple different values will show null in the output.

OR try Aggr(Concat(Idea filed,','),Region,status)

I think the first one should work if we have taken Region and status as dimensions in the chart.

Thanks,

[#6]

OK, i have tried what you suggested and it works, but it's not ideal

I don't want the Idea Number dimension showing, nor do I want one row for each idea,as this make the table far too big

[#7]

Can i insert a line break, rather than a comma?

[Digvijay_Singh]

try chr(10)

[morganaaron]

The other option as mentioned is using Concat. You could use Concat([Idea], chr(10)) and then wrap the cell height to the max number of ideas per combination to show this in a similar idea without needing the ID field.

[#10]

That worked, but I now need to colour Ideas which have the Priority field set as 'High' red.

Is that possible within the same cell? If not, is there a different solution?