Column into single row

[ringovski1]

Hi All,

I have a single table called RDSS(voting) which has a column for each state(state_ab), division(div_nm) & candidate 1 & 2(cand_num) and there is a row for each candiates voting results & the precentage of the total.

What I am trying to is display the results for each state & division in a single row. The current results are displaying the right numbers but on seperate rows. I have tried a combination of different functions and haven't  been able to get them to work and now I out of ideas.

Table Name: RDSS
Sample Data
state_ab,	div_nm,	cand_num,	party_ab,	total_votes,	total_votes_pc,
state1,	name1,	CAND_01,	Party1,	50472,	50.84878953
state1,	name1,	CAND_02,	Party2,	48787,	49.15121047
state1,	name1,	MARGIN,		1685,	1.697579061
state1,	name2,	CAND_01,	Party1,	65000,	54.16666667
state1,	name2,	CAND_02,	Party2,	55000,	45.83333333
state1,	name2,	MARGIN,		10000,	8.333333333
state2,	name3,	CAND_01,	Party1,	53241,	69.39741133
state2	name3	CAND_02	Party2	23478	30.60258867
state2	name3	MARGIN		29763	38.79482266
state1	name4	CAND_01	Party1	12500	71.42857143
state1	name4	CAND_02	Party2	5000	28.57142857
state1	name4	MARGIN		7500	42.85714286

expected results
state_ab,	div_nm,	Party_ab (Cand_01),	total_votes (cand_01),	total_votes_pc (Cand_01),	Party_ab (Cand_02),	total_votes (Cand_02),	total_votes_pc (Cand_02),
state1,	name1,	Party1,	50472,	50.84878953,	Party2,	48787,	49.15121047,
state2,	name2,	Party1,	65000,	54.16666667,	Party2,	55000,	45.83333333,
state2,	name3,	Party1,	53241,	69.39741133,	Party2,	23478,	30.60258867,
state1,	name4,	Party1,	12500,	71.42857143,	Party2,	5000,	28.57142857,

current results
state_ab,	div_nm,	Party_ab (Cand_01),	total_votes (Cand_01),	total_votes_pc (Cand_01),	Party_ab (Cand_02),	total_votes (Cand_02),	total_votes_pc (Cand_02),
state1,	name1,	Party1,	50472,	50.84878953,	,	,	,
state1,	name1,	Party2,	,	,	Party2,	48787,	49.15121047,
state1,	name2,	Party1,	65000,	54.16666667,	,	,	,
state1,	name2,	Party2,	,	,	Party2,	55000,	45.83333333,

I have tried using switch function as calculated columns but as above they don't quite work.

Cand_01 Party =    Switch(RDSS[cand_num],"CAND_01",RDSS[party_ab])

Cand_02 Party = Switch(RDSS[cand_num],"CAND_02",RDSS[party_ab])

Cand_01 Votes = Switch(RDSS[cand_num],"CAND_01",RDSS[total_votes])

Cand_02 Votes = Switch(RDSS[cand_num],"CAND_02",RDSS[total_votes])

Cand_01 pc =Switch(RDSS[cand_num],"CAND_01",[total_votes_pc])

Cand_02 pc =Switch(RDSS[cand_num],"CAND_02",[total_votes_pc])

Thanks for any help.

*Sorry for formatting the web page removed my tables, iv'e added commas to help with formatting.

[daxer-almighty]

To do this you have to:

1) Either find a custom visual that will let you do that (but I doubt you'll find something like this).

2) Create a measure that will return the party of the candidate.

You can easily see that it's not possible to do what you want using the columns from your table and the table/matrix visual because when you drop a column onto the visual this column slices the data in your table. Hence the output you get. If you create a measure that will return this piece of data, then you'll be able to show the different party for each candidate on the same row just the way you want it.