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   Pr...
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.

     

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