Forum Discussion

lp999's avatar
lp999
Regular Visitor
3 years ago
Solved

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! 

Need Help
  • MarkLaf's avatar
    MarkLaf
    3 years ago

    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:

1 Reply

  • MarkLaf's avatar
    MarkLaf
    Super User
    3 years ago

    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: