Is it possible to display a metric at the bottom of a pivot table?

[Badrul]

For simplicity's sake lets assume I have a fact table that says:

Account, Account Name, Amount
1 Blah 100
2 Blah2 50

And a hierarchy table that says:

Account Category
1 Current Assets
2 Current Liabilities

Using this I get to a pivot table that shows me:
Current Assets
Blah 100
Current Liabilities
Blah2 50


I now want to Net working capital to appear at the bottom as a calculation. So:

Current Assets
Blah 100
Current Liabilities
Blah2 50
Net working capital 50


How do I do this when my fact table does not contain any "category" of Net working capital (so even if I insert it into my hierarchy table it won't show up)?

 

[cajste]

I would create 3 calculated measures, one for Current Assets, one for Current Liabilities and then Net working capital as [Current Assets]-[Current Liabilities].

//Caj

 

[Badrul]

Well current assets and liabilities is already calculated for me using my fact table plus a Dim_AccountHierarchyTable.
So my fact table is
- Account number (e.g. 00010)
- CategoryID (pointing to the Dim table which then says current assets, liabilities, Net income etc.)
- $ amount
- Month

So I use Powerpivot to construct a financial statement.
But I'd like to have some financial metrics shown at the bottom of the financial statement. Whenever i create a metric of any sort though and drop it in it automatically inserts a new layer so I get:
Net income
Metric

Current Assets
Metric

and so on.

I guess I can use the Field/Sets thing to then hide these and keep only the last one, but it seems really inefficient to do it like this esp. if I have say 5 or more metrics that I want displayed at the bottom.

 

[cajste]

Sounds like what you have is not a calculated measure but a filter showing the same measure in different contexts. If you create the calculated measures I suggested you will be able to show all three side by side, Current Assets and Current Liabilities showing exactly the same figures that you see now and the third being a formula of the calculated measures. That way you avoid the additional layers that you get now.

The measure for Current Assets would look something like this: =calculate(sum(facttable[$amount]);dim:AccountHierarcyTable[AccountCategory="Current Assets])

Or am I getting you totally wrong?

 

[Badrul]

So it needs to look something like this:

	2010	2011	2012
Current Assets	100	200	200
Non-Current Assets	300	300	200
Total Assets	400	500	400
Current Liabilities	100	100	100
Long-term liabilities	100	100	100
Total Liabilities	200	200	100
Net working capital	0	100	100

<tbody>
</tbody>

And for lines 1-2 & 4-5 you should be able to drill down to account level.
I have till now done this using something similar to the middle section of this article (Alberto Ferrari : PowerPivot and Parent/Child hierarchies )


....

Now - with the method you suggest, I would have to create a measure for every line I need beyond the bottom most level. Not great but ok I can live with that.

However I tried a little test of what you suggested and it seems to me I end up with something that looks more like this:

Current Assets	Current Liabilities	Total Liabilities	etc.
200	100	100

<tbody>
</tbody>

I.e. The measures are listed across the top. In fact every powerpivot book and article I've ever read seems to do this and not address the question of what if you wanted the measures listed as rows, with periods across the top.

Unless I'm misunderstanding something here?

 

[cajste]

You're right, probably not the best way to handle your problem and the possibilities to drill down might be limited.

Listing measures as rows and periods across the top is no problem. Given your example above you should in the Column Labels field of your PowerPivot Field List have a "category" called Values. You can move that category to the Row Labels field and the values you have selected will be listed as rows. You can then add year, month etc. to the Column Labels field.

You should then be able to add your categories or accounts below the values in the Row Labels Field to get a report that looks like the example you gave.

 

[Badrul]

Thanks! That little tidbit of advice actually is great and I haven't seen mentioned anyway; Worse case scenario I end up having to do it a separate Pivot table that sits beneath the main one. Will try it first as part of my existing solution first though. Thanks again