Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Sorting matrix column value based on measure

Hi , 

I have a scenario where i need to sort the column measure value based on another measure in matrix table. 

Here is my scenario. 

Region        Product 1         Product 2      Product 3   Product 4

West             3000                 3500              2000           1500

East               2500                 1200              2300            2500 

Southeast      3500                 4500             3000            40000 

 

I have used top 10 sales for product 1 in the filter condition which gives me Top 10 sales for Product 1 along with other values for other products which are relevant 

Now i need to sort value based on Filter i applied, which is Top 10 Sales for Product 1 . 

Result should be 

Region        Product 1         Product 2      Product 3   Product 4

Southeast      3500                 4500             3000          40000 

West             3000                 3500              2000           1500

East               2500                 1200              2300            2500 

 

Thanks in advance for your help 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,hello rajendraongole1 ,

    Thanks for your prompt reply!

    As rajendraongole1 suggested, we need to create a measure to rank the Product1 based on Top 10 region filter.

     

    Rank_WITH_ ALLSELECTED = 
RANKX (
    ALLSELECTED(  'Table (2)'[Region] ),
    CALCULATE ( SUM ( 'Table (2)'[Product1] ) )
)

     

    Then sort the visual based on that rank measure as shown below:

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

     

    Best regards,

     

    Joyce

     

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

     

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi Anonymous  - Create a measure that will rank the regions based on Product 1 sales. This will help in sorting the regions in the matrix table.

    Rank Measure: 

     

    RankByProduct1 =
    RANKX(
    ALLSELECTED('YourTableName'),
    CALCULATE(SUM('YourTableName'[Product1])),
    ,
    DESC,
    DENSE
    )

    Create another measure Top 10 sales for Product 1.

    Top 10 Product Measure:

     

    Top10Product1 =
    TOPN(
    10,
    SUMMARIZE(
    'YourTableName',
    'YourTableName'[Region],
    "Product1Sales", SUM('YourTableName'[Product1])
    ),
    [Product1Sales], DESC
    )

    take the matrix visualization, you need to sort the matrix by the RankByProduct1 measure.

     

    as per your mentioned, add the region to row and product1,2,3,4 to the value of the matrix,Sort the Region column by the RankByProduct1 measure to ensure the correct order.

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Top 10 Product1 measure is giving me error. Expression refers to multiple columns , cannot be converted to a scalar value 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,hello rajendraongole1 ,

    Thanks for your prompt reply!

    As rajendraongole1 suggested, we need to create a measure to rank the Product1 based on Top 10 region filter.

     

    Rank_WITH_ ALLSELECTED = 
RANKX (
    ALLSELECTED(  'Table (2)'[Region] ),
    CALCULATE ( SUM ( 'Table (2)'[Product1] ) )
)

     

    Then sort the visual based on that rank measure as shown below:

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

     

    Best regards,

     

    Joyce

     

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