Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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:
Creating a user hierarchy from a manager column to use as hierarchy slicer
Regards,
Xiaoxin Sheng
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:
Creating a user hierarchy from a manager column to use as hierarchy slicer
Regards,
Xiaoxin Sheng
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!
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.....
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |