Reporting text

[airranz]

I want to create reports that combine numbers a text. I can not do it with Power Pivot because it only work with numeric fields in the value.
Something like

[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]2010[/TD]
[TD][/TD]
[TD]2011[/TD]
[TD][/TD]
[TD]2012[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]sales[/TD]
[TD]comment[/TD]
[TD]sales[/TD]
[TD]comment[/TD]
[TD]sales[/TD]
[TD]comment[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]1000[/TD]
[TD]Good[/TD]
[TD]2000[/TD]
[TD]Better[/TD]
[TD]1500[/TD]
[TD]OK[/TD]
[/TR]
[TR]
[TD]Germany[/TD]
[TD]200[/TD]
[TD]Bad[/TD]
[TD]500[/TD]
[TD]Good[/TD]
[TD]1000[/TD]
[TD]great[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Is there any way to force Power Pivot to work with text fields?
Which alternative tool could I use similar to power pivot?

 

[tybaltlives]

I dont have power pivot installed, but I think you should reorganize your raw data tables so that it is "rational" - thats a database term. If you do this, a regular pivot table will do the job.

Excel 2010
	A	B	C	D	E
1	Ctry	Year	Sales	Comment
2	USA	2010	1000	good
3	USA	2011	2000	Better
4	USA	2012	1500	OK
5	Ger	2010	200	Bad
6	Ger	2011	500	good
7	Ger	2012	1000	Great
8
9
10	Ctry	USA
11
12	Sum of Sales	Column Labels
13	Row Labels	2010	2011	2012	Grand Total
14	Better		2000		2000
15	good	1000			1000
16	OK			1500	1500
17	Grand Total	1000	2000	1500	4500
Sheet1

 

[tybaltlives]

sorry. spell check. I meant "Relational". lol.

 

[airranz]

I have the data in a relational form, just as you showed. What I want is not a pivot table, to manipulate numbers, but rather a report that shows numbers and text associated to that numbers as I displayed in my example.
With a normal Pivot table I guess is the same problem that with Power Pivot, you can only put Numeric fields, in the Values.

The example is a simplification, my real problems is much more complex, and I like the way to build my reports with Power Pivot, because that give me the interactivity that I need with the slicers, but I would like to complement the numeric values with some text fields that doesn't require agregation. Probably with Power Pivot i can not achieve it, but I guess ther should be some tool with a similar way to build reports

 

[tybaltlives]

USe an array formula to read your table then. Press ctrl-shft-enter to make it work. Here you go:

Excel 2010
	A	B	C	D	E	F	G
1	Ctry	Year	Sales	Comment
2	USA	2010	1000	good
3	USA	2011	2000	Better
4	USA	2012	1500	OK
5	Ger	2010	200	Bad
6	Ger	2011	500	good
7	Ger	2012	1000	Great
8
9
10	Country	2010		2011		2012
11		Sales	Comment	Sales	Comment	Sales	Comment
12	USA	1000	good	2000	Better	1500	OK
13	Ger	200	Bad	500	good	1000	Great
Sheet1

Cell Formulas
Range	Formula
B12	{=INDEX($C$2:$D$7,MATCH(1,($A$2:$A$7=$A12)*($B$2:$B$7=B$10),0),1)}
B13	{=INDEX($C$2:$D$7,MATCH(1,($A$2:$A$7=$A13)*($B$2:$B$7=B$10),0),1)}
C12	{=INDEX($C$2:$D$7,MATCH(1,($A$2:$A$7=$A12)*($B$2:$B$7=B$10),0),2)}
C13	{=INDEX($C$2:$D$7,MATCH(1,($A$2:$A$7=$A13)*($B$2:$B$7=B$10),0),2)}
D12	{=INDEX($C$2:$D$7,MATCH(1,($A$2:$A$7=$A12)*($B$2:$B$7=D$10),0),1)}
D13	{=INDEX($C$2:$D$7,MATCH(1,($A$2:$A$7=$A13)*($B$2:$B$7=D$10),0),1)}
E12	{=INDEX($C$2:$D$7,MATCH(1,($A$2:$A$7=$A12)*($B$2:$B$7=D$10),0),2)}
E13	{=INDEX($C$2:$D$7,MATCH(1,($A$2:$A$7=$A13)*($B$2:$B$7=D$10),0),2)}
F12	{=INDEX($C$2:$D$7,MATCH(1,($A$2:$A$7=$A12)*($B$2:$B$7=F$10),0),1)}
F13	{=INDEX($C$2:$D$7,MATCH(1,($A$2:$A$7=$A13)*($B$2:$B$7=F$10),0),1)}
G12	{=INDEX($C$2:$D$7,MATCH(1,($A$2:$A$7=$A12)*($B$2:$B$7=F$10),0),2)}
G13	{=INDEX($C$2:$D$7,MATCH(1,($A$2:$A$7=$A13)*($B$2:$B$7=F$10),0),2)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[tybaltlives]

PS: THe values in cell B12 and C12 can be copied down and to the right without editing. So you really only need to enter two formulas.

 

[miguel.escobar]

you can have measures with a text output. It'll be something among the lines of this video
http://www.youtube.com/watch?v=AfnCiLXHWzU
the time consuming part would be to define your table with the correct layout/schema so you can create a measure based on that Disconnected Table