Subtotaling based on other fields, at different levels of expansion

[Crawly]

Dear all,
I am trying to use PowerPivot to look at some OLAP data. For this example, I have two dimensions and one measure:
Item dimension: Item Class, Item Group, Item
Transaction Currency: Transaction Currency
Measure: Revenue in Transaction Currency

What I would like to achieve is the following:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]ItemClass1[/TD]
[TD]ItemGroup1[/TD]
[TD]Item1[/TD]
[TD]EUR[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]ItemClass1[/TD]
[TD]ItemGroup1[/TD]
[TD]Item1[/TD]
[TD]USD[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]ItemClass1[/TD]
[TD]ItemGroup1[/TD]
[TD]Item2[/TD]
[TD]EUR[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]ItemClass1[/TD]
[TD]ItemGroup1[/TD]
[TD]Item3[/TD]
[TD]USD[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]ItemClass1
[/TD]
[TD]Subtotal ItemGroup1
[/TD]
[TD][/TD]
[TD]EUR
[/TD]
[TD]400
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]USD
[/TD]
[TD]300
[/TD]
[/TR]
[TR]
[TD]ItemClass1[/TD]
[TD]ItemGroup2[/TD]
[TD]Item4[/TD]
[TD]EUR[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

In words - I want each item to be broken down by currency, and each subtotal to be broken down by currency.

I am able to get this sort of summary when I have the Item dimension expanded up to the Item Group level, but as soon as I expand it to the Item level, the subtotals are no longer split by currency, and instead sum up all of the lines regardless of currency (an obviously undesirable result).

I'd appreciate any ideas as to how to get this done, because I am at a complete loss and I have not been able to Google up a solution.

Thanks,
-C

 

[JavierGuillen]

Hi Crawly,

You can achieve a muli-lines for subtotals by creating a DAX measure for each currency:

EUR:=CALCULATE( SUM(Table1[100]) , Table1[Currency] = "EUR" )

USD:=CALCULATE( SUM(Table1[100]) , Table1[Currency] = "USD" )

When you place them in the 'Values' pane of the pivot table, a 'E Values' group is now automatically created on the column labels pane. You can now drag that one down to the 'row labels'. Finally, you can have the item repeat its values by right clicking on any cell on the item column (in the pivot table) and then go to 'field settings', 'layout and print' and check 'repeat item lables'.

The only drawback is that item/currency combinations with no value will still show (which may be desiable in some scenarios!).


Javier Guillen
Senior BI Consultant
PowerPivot Blog: http://javierguillen.wordpress.com
Twitter: @javiguillen