Multi Table Excel spreadsheet - is it possible?

[BertP]

Our company contains multiple operating units and produces an overall report of these operating units in an Excel spreadsheet format every quarter.

These reports have the same column headings at the top, but are broken into separate tables in the spreadsheet containing data for each operating unit as well as a brief summary of that data (interim totals) at the bottom of those tables

At the very bottom of the entire spreadsheet, it has grand totals for all the intervening tables of each operating unit.

I guess, conceptually, this means the report contains separate tables for each (filtered) operating unit. These have totals underneath them. But each table follows sequentially on the same spreadsheet, and at the very bottom, has grand totals for all the tables of the intervening operating units.

Is it possible to produce a single spreadsheet like this in Power BI? Or at least something similar?

[ppm1]

Yes. Normally, you would add a single table visual with a slicer to allow the consumer to filter by each business unit. In your case, you could create a matrix visual that has your business unit as the parent/outer dimension on rows to get subtotals and a grand total.

Not recommended - However, you could add multiple tables to the report page, each filtered to a different business unit (and then have a visual at the end for the grand total.

Pat

[BertP]

Thanks so much @ppm1 

I played around with the matrix visual & while it doesn't do exactly what I wanted, it gives us some options to work with.

It would be ideal if I could have multiple table columns listed on a single matrix "row," without creating a massive "tree," but I don't think that's possible right?

Thx!

[ppm1]

Not sure what you mean by a single matrix row. Can you provide an example/sketch?

Pat

[BertP]

Hmm, I'm not sure why replies seem to have inverted their order. I'm wondering if my reply became buried. @ppm1 Did the diagram I posted help clarify the direction I was hoping to go?

[ppm1]

Have you tried the option to switch to show measures on rows?

Pat

[BertP]

Thanks again for your reply @ppm1 

I tried out the switch to show measures on rows, but it wasn't what we were looking for.

To describe it differently, for each given operating unit & project within that operating unit, we would also like to display columns for their address/city/state/zip. Placing them on the right side of the matrix doesn't seem right as we don't really want the first item, or summarized values. We're looking for all the unique items to be shown. But placing them on the left as rows, makes them appear as deeper branches of a tree. We really only want the address/city/state/zip to appear as a single, multi column branch in the tree.

I guess the closest I can think would be to create a new column comprising all the values in the address/city/state/zip columns & use that within the matrix, but we would also like to be able to export those values into an excel spreadsheet as separate columns...

Does that make sense?

[ppm1]

It sounds like you are describing a table visual. All values would be shown on the same row for all combinations. If you needed the black column, you could probably do a constant measure and use conditional formatting to alway make it black.

Pat

[BertP]

Thanks again @ppm1 !

OK, probably the easiest representation of the data we need after export to Excel, would be the following image:

(If you could imagine the blank cells on the left of the black square filled with non numeric data, and the blank cells on the right of the black square filled with numeric data.)

I am also envisaging the black square as the separator also in the matrix.

The main difficulty as I see it, is that I would like to include all the fields on the left of the black square as a single row (i.e. in the  matrix visual, to be able to expand it at a single click, rather than each column being represented as a deeper branch of a tree).

The other difficulty is that I would like to include a percentage (CO-OP Cost %) in the data on the right of the black square. Being a percentage, this can neither be summed nor averaged, so I'm guessing I would need to exclude it from the matrix (?)