Income Statement Matrix

[Feilin]

I am creating an income statement, which I want to be in a matrix view (amongst other visuals). I have a couple of issues that I'm trying to solve. Right now I am having a financial model (a separate dimension table) with a 4 columns that place the hundreds of accounts into various categories (2-3 dozen of Sub-sub-categories, about a dozen of Sub-categories, and a handful of overarching Categories). Because each account can exist in several (sub)categories, I have linked it to a list (another table) of all the accounts for a many-to-many relationship. This intermediate table is then linked to the financial data (a facts table) with account, amount and some other columns (date, actual/budget, etc).

I would like to "mix data with measures". For instance, I have (on the top level) Revenue (data), Costs (also data), which are summarised to give the profit (measure), as well as some other derivatives (profit percentages, EBIT, etc, also measures). Then I have some other posts such as Taxes (again data). Of course, I would like the structure of Categories and Sub-categories etc to be intact (at the very least for the data fields, but if possible also the measures). How do I manage to put these measures in the matrix, between the data lines in the best way?

[Anonymous]

Can you create a single table that has the Account Code (which needs to be unique), then 1 column for each level of the heirarchy?  This would allow you to simply build the matrix off this account code listing, but also allow you to link to the data in whatever fashion you have it in.

You could potentially even get Power Query to build this table for you as part of the import.

[Feilin]

I am not sure what you mean, @Anonymous, but I do have a table to connect the Dimension Table (the model) with the Facts Table (the data). Since the model has the same account in several places and the data obviously repeats the accounts for dates etc, I need it to be a many-to-many relationship, and for that I use that "intermediary" table. In the model I have 1 column for each level; do you mean it should be in the intermediary Accounts Table (i.e. a list of the Account Codes and their names)?

Could you explain a bit more how it would help me mix data with models in the matrix visual? Or perhaps provide an example or tutorial from elsewhere?

[Feilin]

And for information, this is something like what I'm trying to achieve. These are the data. I would like to show the Actual (data) as a column, but then add a Gross Profit percentage for the Actual data measure, somewhere in the Gross Profit Category, or at least right after, so it is logically connected to it. If that is completely impossible, I would like to at least put all the measures at the end, but still in the Actual column, so that I can have one calculated measure for Actual, one for Budget, etc, for several items (such as GP%, etc). But I'm really struggling for how I can combine data with measures in this way.

[Anonymous · Accepted solution]

My suggest is, rather than try to set up a many to many relationship, is to build a table that holds all of the heirchy details in it by your account code.  Then you can use these columns within your slicers and simply connect this single account table to your actuals table.

I'm suggesting you effectively merge 2 of your tables into one, so you can end up with a single table created as part of the import to ensure you have a 1 to many relationship.