cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tnoflahc Frequent Visitor
Frequent Visitor

Multiple tier rollup in Matrix Preview

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Multiple tier rollup in Matrix Preview

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
3 REPLIES 3
Community Support Team
Community Support Team

Re: Multiple tier rollup in Matrix Preview

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
tnoflahc Frequent Visitor
Frequent Visitor

Re: Multiple tier rollup in Matrix Preview

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 Frequent Visitor
Frequent Visitor

Re: Multiple tier rollup in Matrix Preview

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.....