Forum Discussion

andrnkls's avatar
andrnkls
New Member
8 years ago

Custom Hierarchy Visual

I am trying to create a custom hierarchy based on values in a column from my data sheet.   My data consists of sales for 15 different departments per month.   I want to be able to categorize thes...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi andrnkls,

 

You can create a separate 'Sales Band' table stored in an Excel file. Load this table together with department sales table into desktop. 

 

In desktop, create a calculated table with below DAX. Then, drag corresponding fields into Matrix.

CorssTable =
FILTER (
    CROSSJOIN ( Department_sale, 'Sales Band' ),
    ( Department_sale[Sales] >= 'Sales Band'[Min]
        && Department_sale[Sales] < 'Sales Band'[Max] )
        || (
            Department_sale[Sales] >= 'Sales Band'[Min]
                && 'Sales Band'[Max] = BLANK ()
        )
)

 

This way, to change the Sales Band ranges or add a new Sales Band Range or Remove a Sales Band range, you only need to update the 'Sales Band' table in source excel file. Then, manually refresh this table view in desktop, the matrix visual will update automatically.

 

Regards,

Yuliana Gu

  • andrnkls's avatar
    andrnkls
    New Member
    8 years ago

    Thanks. This does help in a way.

     

    Is there there no tool bar option for anything like this though? I tried grouping, binning, what if parameters, and hierarchy, but none achieve the same customization.

     

    The crosstable option is a better alternative solution for what I need where I can change the bands without having to alter any DAX or M code. I would still like to avoid having to create any extra tables and relationships.

     

    If I wanted to also create another custom hierarchy like the sales band one and also have it be displayed in the same matrix as the Sales Band one, is that possible?

     

    The other hierarchy I am looking to create is a custom region that consists of various countries. I want the region name to be displayed in the matrix with a total value and then be able to drill it down and show the invididual countries that are part of it with their sales. The tricky part is, some countries are only apart of the region for certain dates, but those countries can still have data when they are not a part of the region. Would a crosstable also work for this?

     

    Ex.

     

    For the range for 10/31/2017 to 2/28/2018

    Region X consists of:

    USA

    Germany

    Mexico

    Canada

    Australia

     

    But Australia joins the region on 12/31/2017 and Mexico leaves the region on 1/31/2017. Both countries still have data before and after those dates. How would I be able to control a hierarchy like the above. New countries may also join and leave in the future, so I need the ability to alter these on the fly. Assume I can do the same through a data sheet?