Merging dimensions to do a thing

Hi Angelia,

Thanks for the response!

To clarify, the report I'm looking to generate ought to be identitical to the matrix visual shown in I think the second screenshot of my OP. A table would be simpler--and that may indeed be a viable intermediate step--, but ultimately, I need to be able to drill down and have subtotals and all that, which functionality I do not believe is available on tables.

Additionally, my data structure has changed somewhat in my own Excel-based data warehouse, such that, instead of having columns for Actual, Budget, Forecast, and Prior Year, there is now a "time-period" column that specifies Actual, Budget, etc. and then a Values column. I made this change because it more closely reflects how the data is coming in from our consolidation software.

It might be helpful, too, if I include some information about how the data comes in from the consolidation software. I know I mentioned it briefly in the OP, but here's a little more detail.

We have a (what seems to me, anyway) textbook star schema coming into PBI, with a fact table and related dimension tables, which hold the hierarchies of the different dimensions referenced in the OP.

So, the fact table is arranged thus: 

And then for each _ID, there is a corresponding dimension table, "Account Hierarchy," "Time Period Hierarchy," etc.

Within each dimension table, a hierarchy is specified:

The problem I'm encountering when I try to use the data as-is, is that some sectors' hierarchy is entirely within the Entity dimension, so City is one level within entity, State is a level higher within entity, Region is yet another level higher within entity, and so on.

Other sectors use a combination of Entity and Market. Yet others use Entity and Product.

When I create a matrix visual using the data as I'm given it, adding, say, Product levels to those sectors that don't use Product to differentiate between sub-sectors adds data to the visual that I don't want.

I believe what I need to do is create a new data table in PBI that is nearly identitical to the one I've created in Excel. Basically, I would combine all these different dimensions and their hierarchies--entity, market, product--so that the level in Entity that corresponds to the City level is combined with the level in Product that also corresponds to the City level.

I know that's a lot to read through, but hopefully that clarifies what I'm working with and what I'm trying to do.

Conceptually, I'm fairly certain of what needs to be done; I just don't know how to implement it in PBI.

Thanks for your time!