I have a dataset with 2 columns:
A B Balance
18 26 25
26 25 50
26 17 75
19 26 50
29 18 100
I want to be able to create a pivot table where I can group (and count, sum etc) on a calculated field which treats values in column A or B as the same. For example, resulting pivot will look like the following:
AorB count sum(Balance)
17 1 75
18 2 125
19 1 50
25 1 50
26 4 200
Please help!!!!
You can do that with a union. Drag your table into the data pane, then drag it over top of it again to create a union. It'll look something like this:
Then create a calculated field like this (make sure to convert it to a dimension, if it's appearing as a measure):
AorB
IF [Table Name]="Sheet1" THEN
[A]
ELSE
[B]
END
Then you can do your aggregation:
See the sample data and workbook attached.
