Matrix Row Custom Grouping

[oshello]

Hi,

I have a problem where I'm trying to customize the order of grouping of the rows in a matrix visual without reversing the labels. It's a bit hard to explain so I have an example.

That's my current result, but I want to make it so that everything that is labeled 'H299' is together even with different names. So lets say the [Names] are 'ITRX', 'Treasury' and [Code] are 'H299', 'B161', etc. I want to group all items with the same code together regardless of their names, but the resulting matrix row expansion will still be in the form of [Name] -> [Code]. Then I want to pick the row label [Name] to be 'ITRX' instead of 'Treasury'. So 'Treasury' never gets picked if it shares the same code with an item with another name.

So the end result would be [ITRX] -> [H299] -> (lists all 4 items with code H299) in my matrix. Any help would be appreciated.

[oshello]

Ok. Here is the input :

Here is my desired output (shown in Excel just for my own convenience) vs what I have in PowerBI right now.

As you can see the difference is just that I want to group both securities with code A001 together despite having different names/company, but I want to preserve the order of the matrix row levels as [Company] -> [Code] -> [Security].

Here are the files.

output(BI) input (excel)

[Ritaf1983]

Hi @oshello  if I understand you correctly you need to switch off subtotals on an unnecessary level.
Please see an attached picture of how to manipulate row subtotals levels.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[oshello]

No, it doesn't change anything at all. @Ritaf1983 Im not asking for the row subtotals, Im asking for the grouping.

[Ritaf1983]

Hi @oshello  finally I got it, unfortunately, we don't have the option to customize groups without 
some logic.
So if the "trouble" is just with one / few codes you can add a "helping" column which will categorize your code as you need:

Matrix_Help_column = if('Input (2)'[Code]="A001","BNKEA",'Input (2)'[Company])

then use that column in your matrix :

if you have many codes like this, you need to prepare a glossary table with the code and company and merge it with the data table .

Link to updated file 
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[oshello]

I see, I understand. But is it possible if all securities with the [Company] 'Treasury' is set to always belong to another group that shares its [Code]? As in, in the resulting matrix like you just made, there is no 'Treasury' row, because all securities that are treasuries always share their code with another security. Can this be the logic to customize groups? 

[Ritaf1983 · Accepted solution]

Hi @oshello 
Yes,you can try  create calculated column like :

RelatedCompany =

VAR CurrentCode = 'Input (2)'[Code]

VAR CurrentCompany = 'Input (2)'[Company]

RETURN

    IF(CurrentCompany = "TREASURY",

        CALCULATE(

            MAX('Input (2)'[Company]),

            FILTER(

                'Input (2)',

                'Input (2)'[Code] = CurrentCode &&

                'Input (2)'[Company] <> "TREASURY"

            )

        ),

        CurrentCompany

    )

I updated the sample file as well.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ritaf1983]

Hi @oshello 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523