Matrix Help

[jslaws02]

Thank you in advance.

I have a dataset with the following relationships:

and I'm trying to create a matrix that will have a Heirarchy of Cost Type > Account Name/Subcontractor Classification. That is to say if the Cost Type is Subcontractor, I'd like to show the Subcontractor Classification, but if the cost type is anything else then I'd like to show the Account Name. I'm looking for suggestions on how to create this 2nd level of the heirarchy with the tables shown above.

[uelber]

If you are looking to compare actual against budget, you want to make sure the relationships between the tables are set up correctly. Also, you want to consider the granularity between the budget and the ledger tables. For example, if the budget data is at a monthly level and the ledger data is at a daily level, you need to ensure that the aggregation in the matrix makes sense for both.

[uelber]

Hello! Hello! \o/

One option to achieve this would be creating a new calculated column in the Ledger table that decides whether to show the Account Name or the Subcontractor Classification. In the example below, I'm calling the new column Second Level, please make sure to give it a proper name. Also, please remember that calculated columns will add up in the storage size.

I hope that helps.

[jslaws02]

When I use this column in my matrix along with values from the budget table (see diagram above) I get a slew of duplicate values.