Custom Hierarchy Visual

[andrnkls]

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]

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]

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?