Multiple tier rollup in Matrix Preview

[tnoflahc]

Hi everybody,

I'm trying to produce a report using the Matrix Preview. This report would show sales information in a format similar to the following:

Maine

   Brunswick

   Portland

   Bangor

California

   Monterey

   San Jose

   Salinas

And I've got my data set up to do that, and it works just fine. That's cool.

Except one of my states is different from the others. This state is set up thus:

Alabama

   Montgomery

      Old Cloverdale

      The Beltline

      Downtown

         Bar District

         Industrial District

Additionally, I'd like to be able to show the rollup to specific regions of the U.S. So, states would not be the highest tier, but rather you'd see something more like:

United States

   New England

      Maine

         Brunswick

         Portland

         Bangor

   Central Pacific Coast

      California

         Monterey

         San Jose

         Salinas

   Southeast

      Alabama

         Montgomery

            Old Cloverdale

            East Boulevard

            South Boulevard

            Downtown

               Bar District

               Industrial District

         Birmingham

Is this a hierarchy that can be correctly shown using Matrix Preview? If so, how would I go about structuring my data to show it? Or, is my data structured correctly, and there's something else I'm missing?

Presently, I have the following coming out of Excel:

A fact table that shows sales coming out of each leaf-level segment, so something like this (there's a date column, too, but this is getting tedious to reproduce on here):

Brunswick - $xx

Portland - $xx

Bar District - $xx

etc.

A table that shows the relationships:

Brunswick - Maine

Portland - Maine

Bangor - Maine

Maine - New England

New England - United States

Monterey - California

San Jose - California

Salinas - California

California - Central Pacific Coast

Central Pacific Coast - United States

Old Cloverdale - Montgomery

East Boulevard - Montgomery

South Boulevard - Montgomery

Bar District - Downtown

Industrial District - Downtown

Downtown - Montgomery

Montgomery - Alabama

Birmingham - Alabama

Alabama - Southeast

Southeast - United States

I feel like this is data structure 101 here, but for the life of me, I cannot get this to work correctly in my Matrix Preview.

Any help is appreciated.

[v-shex-msft · Accepted solution]

HI @tnoflahc,

I think you can try to path function to analysis your columns.

1. Use path to analysis hierarchy relationhship.
2. Create calculated columns based on above level.
3. Use these columns to build custom hierarchy.

Reference link:

Parent-Child Hierarchies

Creating a user hierarchy from a manager column to use as hierarchy slicer

Regards,

Xiaoxin Sheng

[tnoflahc]

Hi @v-shex-msft, thanks for the tip! So, using the first link, I figured out how to structure my tables in a way to kind of produce the results I needed. I now have something like this:

Date || City || State || Region || $Amount

And that seems to be working as expected.

Thanks again!

[GH]

Can someone help me with a very similar problem?  It's an organizational hierarchy, so there are different levels under each person.  I have the data flattened out with a path (CEO|ManagerName|SupervisorName|ResourceName|)  but I'm not clear on how to use this in the Matrix visual to expand each level and display corresponding values.....