Forum Discussion
Income Statement Matrix
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.
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.
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?
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.
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.