Mulitple row expression calculations in Crosstab/Pivot table

[#1]

Given the following dataset (assuming current month is September 2013);

UserID	TransactionType	TransactionDate	Amount
1	Online	01/01/13	6
1	Online	01/01/13	5
1	Offline	09/01/13	4
1	Offline	01/01/13	2
2	Online	01/01/13	1
2	Online	01/01/13	2
2	Online	01/01/13	4
3	Online	09/01/13	3
3	Online	09/01/13	4
3	Online	01/01/13	5
3	Online	01/01/13	6
3	Offline	01/01/13	3
3	Offline	01/01/13	8

I am trying to produce a table such like;

		Transaction Type
		Online		Offline
UserID		MTD	YTD	MTD	YTD
1	SumAmount	-	5.5	4	6
	MaxAmount	-	6	4	4
2	SumAmount	-	7	-	-
	MaxAmount	-	4	-	-
3	SumAmount	7	18	-	11
	MaxAmount	4	6	-	8

I have been successful in producing this table for the 'SumAmount' metric alone, but I have been unsuccessful in getting the 'SumAmount' and 'MaxAmount' in different rows of the same table.

So far I have been using a pivot table (thanks to advice on other threads) with UserID and Transaction Type as dimensions and sum of Amount in MTD period and sum of Amount in YTD period as expressions and dragging and dropping the rows to columns.

Does anybody have any clues as to how to get the Sum Amount and Max Amount as different rows in the same table?

I have provided an example dataset here, the actual dataset has upwards of 30million rows so a solution outside of altering the load process would be more preferable.

TIA.

[Gysbert_Wassenaar · Accepted solution]

You can try a synthetic dimension, but I don't think you want this with 30 million records. Performance is likely to be dramatic, if you don't get an allocated memory exceeded error first that is. The alternative is to use four expressions and display them all four as rows or all four as columns.