Subtotaling Financial Data

[RagnarokX66]

I have a set of financial data from an accounting database generated by a stored procedure that I am pulling into Power Pivot to generate various types of financial reports.

The key structure of the data is as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]SortOrd[/TD]
[TD]Type
[/TD]
[TD]Group
[/TD]
[TD]Account
[/TD]
[TD]Entity
[/TD]
[TD]Amt
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]TI
[/TD]
[TD]Revenue:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]SU
[/TD]
[TD]Rent
[/TD]
[TD]500100
[/TD]
[TD]EntA
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]SU
[/TD]
[TD]Escalation
[/TD]
[TD]500200
[/TD]
[TD]EntB
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]SU
[/TD]
[TD]Escalation
[/TD]
[TD]500300
[/TD]
[TD]EntA
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]TO
[/TD]
[TD]Total Revenue
[/TD]
[TD][/TD]
[TD]EntA
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]TO
[/TD]
[TD]Total Revenue
[/TD]
[TD][/TD]
[TD]EntB
[/TD]
[TD]200
[/TD]
[/TR]
</tbody>[/TABLE]

The TI lines are purely descriptive..."Titles." TO type lines are equal to the sum of SU lines but in a user defined manner. SU lines can equal any combination of SU lines as defined by SU ID's stored in the accounting database. The stored procedure calculates the TO lines which is great but it means that the values are hardcoded and don't respond to changes in filters on account number. I was considering bringing the SU ID into the dataset and then trying to write a measure that would allow the TO calculation to respond to filters. Something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]SortOrd[/TD]
[TD]Type
[/TD]
[TD]SUID
[/TD]
[TD]Group
[/TD]
[TD]Account
[/TD]
[TD]Entity
[/TD]
[TD]Amt
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]TI
[/TD]
[TD][/TD]
[TD]Revenue:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]SU
[/TD]
[TD]1
[/TD]
[TD]Rent
[/TD]
[TD]500100
[/TD]
[TD]EntA
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]SU
[/TD]
[TD]2
[/TD]
[TD]Escalation
[/TD]
[TD]500200
[/TD]
[TD]EntB
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]SU
[/TD]
[TD]3
[/TD]
[TD]Escalation
[/TD]
[TD]500300
[/TD]
[TD]EntA
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]TO
[/TD]
[TD]1-3
[/TD]
[TD]Total Revenue
[/TD]
[TD][/TD]
[TD]EntA
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]TO
[/TD]
[TD]1-3
[/TD]
[TD]Total Revenue
[/TD]
[TD][/TD]
[TD]EntB
[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]

My thinking is to just bring in the inputs that are needed to perform the TO calculation but I don't see a clear path from here to get the desired results.

Is it clear what I'm trying to do here? Does anyone have experience with this kind of thing?

Thanks

 

[ImkeF]

There's a method that delivers the results in a pivot-table-report very neatly, but requires quite a lot of efford: Profit & Loss–The Art of the Cascading Subtotal - PowerPivotPro

Or you use some simple stupid method that might create some unnecessary subtotals in the Pivot tables, but brings perfectly fine formatted reports when used with cubefunctions:

You allocate the levels (and their sort order) to each account. Then you just have to drag in the needed levels into your pivot, followed by the description and account numbers. If you sort the level-columns by their respective sort-order-columns the row-layout will just turn out fine.

You need just one simple measure that aggregates everything correctly: SUM(Amt)

In order to get rid of unwanted sub-totals you could use conditional formatting (white text-colour) or you switch to a report design with cubefunctions. But then you have to remember to squeeze in new accounts into the reports manually (there are dynamic approaches here as well, but they require some more description – should write a blogpost sometime)

 

[Matt Allington]

If you want to know what is possible, watch this excellent video from Derek Rickard

http://youtu.be/ojHZkWkEY7Q

It is light in DAX detail, but it may inspire you to aim high.

 

[RagnarokX66]

Thanks for the responses. I did take a look at the Cascading Subtotals article. My total lines however are not necessarily in a "running" pattern. They only sum the specified subtotal lines- they don't need to sum everything that came before it in sequence.

 

[ImkeF]

So you can use the other method then.
Take a table like in my example and connect it with your big table on "Account" each.
Then drag the field "Level1" into the row section of your pivot. After that the "Group"-field from your big table and other columns if needed.

 

[RagnarokX66]

So you can use the other method then.
Take a table like in my example and connect it with your big table on "Account" each.
Then drag the field "Level1" into the row section of your pivot. After that the "Group"-field from your big table and other columns if needed.

I'm not sure this one will work for me. I see what the idea is but one GL account can technically exist in more than one "level 1" grouping. Wouldn't this prevent a relationship between the "fact" table and the "financial format" lookup table?

I feel like maybe two "lookup" tables are needed in addition to the "Fact" table. Maybe something like the below. If each were directly or indirectly related to the fact table based on the SortOrd field alone would we be able to get the desired results in a pivot table?

[TABLE="width: 500"]
<tbody>[TR]
[TD]SortOrd
[/TD]
[TD]Type
[/TD]
[TD]Subtotal ID
[/TD]
[TD]Group
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]SU
[/TD]
[TD]1
[/TD]
[TD]Rent
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]SU
[/TD]
[TD]2
[/TD]
[TD]Escalation
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]TO
[/TD]
[TD][/TD]
[TD]Total Revenue
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]SortOrd[/TD]
[TD]Type
[/TD]
[TD]Subtotal ID[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]TO
[/TD]
[TD]1
[/TD]
[TD]Total Revenue
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]TO
[/TD]
[TD]2
[/TD]
[TD]Total Revenue
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ImkeF]

No, the technique I suggested implies that one account can occur only once - in a level. Otherwise you had to find a way to reconcile aggregations.

So if you want to appear one account in multiple subtotals (which is completely normal in accounting), you would need another level.

Of course there are other techniques, but none of them would work with a "stupid measure" like SUM(Amt). Matt and I have linked to some of them. This is not simple.

I cannot see on which field you would link your above mentioned tables to your main table. You need a column/field with unique values.

 

[RagnarokX66]

Table1
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]SortOrd[/TD]
[TD]Type[/TD]
[TD]Subtotal ID[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SU[/TD]
[TD]1[/TD]
[TD]Rent[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]SU[/TD]
[TD]2[/TD]
[TD]Escalation[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TO[/TD]
[TD][/TD]
[TD]Total Revenue[/TD]
[/TR]
</tbody>[/TABLE]

Table2
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]SortOrd[/TD]
[TD]Type[/TD]
[TD]Subtotal ID[/TD]
[TD]Group[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TO[/TD]
[TD]1[/TD]
[TD]Total Revenue[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TO[/TD]
[TD]2[/TD]
[TD]Total Revenue[/TD]
[/TR]
</tbody>[/TABLE]


If Table2 were related to Table1 on SortOrd and the Fact Table were related to Table1 on SortOrd, could appropriate measures be written to obtain the needed results?

 

[ImkeF]

Maybe.
But this is a very exotic way to build your data model (many-one-many). So I assume that you're not familiar with the standards here (Star and Snowflake)?

Or is my understanding of your proposed model wrong?

 

[RagnarokX66]

Maybe.
But this is a very exotic way to build your data model (many-one-many). So I assume that you're not familiar with the standards here (Star and Snowflake)?

Or is my understanding of your proposed model wrong?

I am not too familiar with the star/snowflake schemas and the corresponding pivot table behavior

So if I had Table2 and the fact table both linked to Table1 on the sortord field, if I then created a pivot table with the SortOrd and Group fields from Table1 in the rows section and the Amt field from the fact table in the values section, would it then sum the Table1.[TO] type lines appropriately as filters are applied?