Showing results for 
Search instead for 
Did you mean: 
andrnkls Frequent Visitor
Frequent Visitor

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 these departments based on sales ranges.


For example:


Sales Band 1 = 0 - 100k

Sales Band 2 = 100k - 250k

Sales Band 3 = 250k - 500k

Sales Band 4 = 500k - 750k

Sales Band 5 = 750k+


In the Matrix report view, I want to be able to drill down the various Sales Bands to show which departments fall under them. But I also want to easily be able to change the Sales Band ranges or add a new Sales Band Range or Remove a Sales Band range.


Add Sales Band 6 and change Sales Band 5 range:

Sales Band 1 = 0 - 100k

Sales Band 2 = 100k - 250k

Sales Band 3 = 250k - 500k

Sales Band 4 = 500k - 750k

Sales Band 5 = 750k - 800k

Sales Band 6 = 800k +


Combine Sales Bands 1 and 2:

Sales Band 1 = 0 -250k

Sales Band 2 = 250k - 500k

Sales Band 3 = 500k - 750k

Sales Band 4 = 750k+


Is there a way through a slicer, visual, what if parameter, or anything else to achieve this? I need a more user friendly solution for this. I can do this by going into edit queries and creating a new column and then doing a bunch of IF/THEN/ELSE, but I want to avoid this path.



v-yulgu-msft Super Contributor
Super Contributor

Re: Custom Hierarchy Visual

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



Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
andrnkls Frequent Visitor
Frequent Visitor

Re: Custom Hierarchy Visual

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?




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

Region X consists of:







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?










Helpful resources

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 289 members 3,271 guests
Please welcome our newest community members: