Forum Discussion

cn4422's avatar
cn4422
Helper V
5 months ago
Solved

Sorting specific column in matrix visual

Hello,

 

I'm trying to sort my matrix table (link to Power BI File) by a specific column, in my case Leads for ContactGroup "A".

In the past is was possible to simply add a measure as a tooltip and use it for sorting, but that doesn't work anymore.

 

This is my table.

 

It should look like this, but without the SumLeads A Column. 😄

 

 

I was wondering if somehow it's possible to do with visual calculations... but I couldn't find a possible solution and am at a loss now.

 

Any input is much appreciated. 🙏

 

Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    5 months ago

    Create a measure that calculates Leads for ContactGroup A:

    Sort Leads A =
CALCULATE(
    [Leads], 
    'ContactGroup'[ContactGroup] = "A"
)

     

    Add this measure to the matrix:

    • Put it in Values

    • Turn off visibility:

      • Set column width very small, or

      • Rename to blank, or

      • Hide via conditional formatting (font = background)

    Then: Matrix → More options (…) → Sort by → Sort Leads A

5 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    5 months ago

    Create a measure that calculates Leads for ContactGroup A:

    Sort Leads A =
CALCULATE(
    [Leads], 
    'ContactGroup'[ContactGroup] = "A"
)

     

    Add this measure to the matrix:

    • Put it in Values

    • Turn off visibility:

      • Set column width very small, or

      • Rename to blank, or

      • Hide via conditional formatting (font = background)

    Then: Matrix → More options (…) → Sort by → Sort Leads A

    • cn4422's avatar
      cn4422
      Helper V
      5 months ago

      cengizhanarslan @  Thanks for your replay. I actually thought about that too, but I was wondering if there is a "cleaner solution" as well

       

      But if nothing else comes up I will either do that or make separate measures for every columen cause then I can sort individually by everything.

       

      Edit:
      I think I'll go with this. In addition, I formatted my measure with ";;;" so that the one column with values makes the values "invisible" as well.

  • Hans-Georg_Puls's avatar
    Hans-Georg_Puls
    Super User
    5 months ago

    Hi cn4422 ,

    a quick solution would be to add "SumLeads A" as Value, switch off "text wrap" for values and column, reduce the column width of all "SumLeads A" columns to zero and adjust the sort by setting of the matrix visual. Only the little triangle couldn't be removed:

    Does that help you?

    • cn4422's avatar
      cn4422
      Helper V
      5 months ago

      Hans-Georg_Puls  Thanks for your replay. I actually thought about that too, but I was wondering if there is a "cleaner solution" as well.

       

      But if nothing else comes up I will either do that or make separate measures for every columen cause then I can sort individually by everything.

  • danextian's avatar
    danextian
    Super User
    5 months ago

    Hi cn4422 

    Sorting by different columns is only possible when those columns aren’t coming from a dimension. Once a measure is broken down by a dimension (like categories), individual sorting per category isn’t supported. The visual will sort using the total column, which means the total must reflect the value of the specific category rather than the aggregate of all categories. Also, adding a measure as a tooltip has generally not been supported for matrix and table visuals (it works only with some other visuals).

     

    Sample measure to return the value of A at the total column.

    new measure =
IF (
    NOT ( HASONEVALUE ( 'table'[ContactGroup] ) ),
    CALCULATE ( [leads measure], KEEPFILTERS ( 'table'[ContactGroup] ) = "A" ),
    [leads measure]
)

     

    Of course, this means that the total shows the value of A only.