Forum Discussion

oshello's avatar
oshello
Frequent Visitor
3 years ago
Solved

Matrix Row Custom Grouping

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.

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    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

7 Replies