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]

I haven't used a model like this before and I'm not able to make it up in my mind how this would work.
So sorry, no help from me here.