cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Data Hierarchy - Subcategory

Hi all! I am trying to create a subcategory into dropdown list using data hierarchy (please no complicated formulas 🙂
I creted two column (same table) but once created data hierarchy they won't  merge. What I am trying to achieve is below:
Column 1 Column 2     Cost
David        John team   10
Giovy        John team   15
Annie        -                   10
Laura        -                    10

I would like to create a subcategory for John's team with David and Giovy . So graphs will break down into whole team of John (tot costs 25) and by member (Giovy 15, David 10). Hope this is clear! I tried withouth using hierarchy but this has created  subcategory for Annie and Laura as blank! Please help!

1 ACCEPTED SOLUTION
Solution Sage

Based on your description, it sounds like you want a particular output on a Matrix visual, not a graph per se. Is that right?

Assuming yes, one approach would be to copy your values from Column 1 into Column 2 where Column 2 is currently blank. You can do that in your source data or with a calculated column like the below:

``Col2 w/ Fill In = IF( LEN( 'Table'[Column 2] ) > 0, 'Table'[Column 2], 'Table'[Column 1] )``

Your matrix with just a regular quick measure on Cost would look like:

So the trick now is to hide the child item when it is the same as the parent item (i.e. when [Column 1] = [Col2 w/ Fill In]). You can do this by using the following measure in lieu of the default aggregation on Cost:

``````Cost_Condense Individuals =
IF(
NOT ISFILTERED( 'Table'[Column 1] )
|| SELECTEDVALUE( 'Table'[Column 1] )
<> SELECTEDVALUE( 'Table'[Col2 w/ Fill In] ),
SUM( 'Table'[Cost] )
)``````

Which gives you the following result in a matrix:

Solution Sage

Based on your description, it sounds like you want a particular output on a Matrix visual, not a graph per se. Is that right?

Assuming yes, one approach would be to copy your values from Column 1 into Column 2 where Column 2 is currently blank. You can do that in your source data or with a calculated column like the below:

``Col2 w/ Fill In = IF( LEN( 'Table'[Column 2] ) > 0, 'Table'[Column 2], 'Table'[Column 1] )``

Your matrix with just a regular quick measure on Cost would look like:

So the trick now is to hide the child item when it is the same as the parent item (i.e. when [Column 1] = [Col2 w/ Fill In]). You can do this by using the following measure in lieu of the default aggregation on Cost:

``````Cost_Condense Individuals =
IF(
NOT ISFILTERED( 'Table'[Column 1] )
|| SELECTEDVALUE( 'Table'[Column 1] )
<> SELECTEDVALUE( 'Table'[Col2 w/ Fill In] ),
SUM( 'Table'[Cost] )
)``````

Which gives you the following result in a matrix:

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors