How to create Crosstable with multiple dimensions and multiple measures ?

[Anonymous]

I have attached the sample excel file which needs to be transformed using crosstable.

can anyone please help me out here.

Regards,

Ranjitha

[Lisa_P · Accepted solution]

Hi Ranjitha,

I would do this:
[Sample]:
CrossTable(Month, Amount, 4)
LOAD
[A],
[B],
[C],
'Actuals' as Type,
[Jan],
[Feb],
[Mar],
[Apr],
[May],
[Jun],
[Jul],
[Aug],
[Sep],
[Oct],
[Nov],
[Dec]
FROM [lib://Downloads/sample.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

CrossTable(Month, Amount, 4)
LOAD
[A],
[B],
[C],
'Budget' as Type,
[Jan1] as Jan,
[Feb1] as Feb,
[Mar1] as Mar,
[Apr1] as Apr,
[May1] as May,
[Jun1] as Jun,
[Jul1] as Jul,
[Aug1] as Aug,
[Sep1] as Sep,
[Oct1] as Oct,
[Nov1] as Nov,
[Dec1] as Dec
FROM [lib://Downloads/sample.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

CrossTable(Month, Amount, 4)
LOAD
[A],
[B],
[C],
'Forecast' as Type,
[Jan2] as Jan,
[Feb2] as Feb,
[Mar2] as Mar,
[Apr2] as Apr,
[May2] as May,
[Jun2] as Jun,
[Jul2] as Jul,
[Aug2] as Aug,
[Sep2] as Sep,
[Oct2] as Oct,
[Nov2] as Nov,
[Dec2] as Dec
FROM [lib://Downloads/sample.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);