Changing sorting in matrix

[hashtag_pete]

Hello everyone, 

two things I can't get my head around, maybe you can provide explanation / solution. 

I have a matrix where I display the sum of a column and a percentage difference of two columns. 

1. I want to sort the matrix alphabetically by category and then top down by percentage. How?
2. When I add to the percentage calculation a -1, it changes automatically the sorting (which I don't want) in a way I don't understand. Has someone an explanation?

here I have the divide function with -1

divide function with -1

here I have the divide function without -1

divide function without -1

I also attach my pbix so you can try out - just change the -1 in the end of the divide function. 

Thanks for your help.

best regards

hashtag-pete

[hashtag_pete]

@v-jingzhang 

it makes totally sense what you say about the formular returning -100% instead of blank. Thing is, I don't want to see those cases, so I thought I wrap it in an if clause, saying if result is -100% then return blank, otherwise return result. But  this does not work - do you have any idea why or how I can get rid of these lines?

thanks in advance

[v-jingzhang]

Hi @hashtag_pete 

I think your idea should work. Anyway try this DAX

Sales Amount vs Extended Amount = 
VAR __result = DIVIDE ( SUM ( Sales[Sales Amount] ), SUM ( Sales[Extended Amount] ), 0 )
RETURN
    IF ( ISBLANK ( __result ), BLANK (), __result - 1 )

Regards,

Jing

[hashtag_pete]

Thanks a lot Jing, made my day!

[v-jingzhang · Accepted solution]

Hi @hashtag_pete 

Currently it is still not supported to sort by multiple columns in a Matrix visual.

While this is supported in a Table visual. If you can conver the matrix into a table, you can Shift + click the column header you would like to add next in the sort order to add more columns to the sort order. See Power BI Desktop March 2020 Feature Summary - Multi-column sort for tables

I found a solution with DAX in this thread (Matrix multiple Sort columns). You can tweak the measure and add it to the matrix, then sort by this measure column. You will probably hope to hide this measure column but I haven't found a good way to do that. 

For the second question, the sorting isn't changed. The highlighted two product rows disappear in the second image because when you have the divide function without -1, it returns blank result for these two products (as their SUM(Sales[Sales Amount]) result is blank). When the function has -1, their results are -100% not blank, so they appear. 

If you want to always show rows with blank values, you can right click on Category or Product field and select Show items with no data option. Or you can also add +0 to the function without -1, then blank values will be converted into 0 and appear in the matrix. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[hashtag_pete]

and here the .pbix
https://drive.google.com/file/d/1mIS00BraX11FXko_RUfni6y48V8Xl8CE/view?usp=sharing