Can "Sort by measure" work if not all the matrix column has this measure?

[YunJ]

Hi,

I have a measure named test1, and only Lip Stick has test1 value. Now I would like to Sort the visual by test1 as descending, which should be MNY, 3CE, LRL MU. But now as you can see it didn't sort by test1. Is it because I don't have test1 value for all the sub-category?

Thanks for your time.

[Pragati11]

HI @YunJ ,

What is the data-type of "test1"? Is it numeric or text?

If it is not numeric, modify the datatype and try sorting again.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[YunJ]

Hi @Pragati11 

The test1 is "Whole number"

[Pragati11]

Hi @YunJ ,

If I see your new screesnhot, it looks like it is sorted on "test1" column in descending order.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[YunJ]

I tried to adjust the measure so that each sub-category can have value, but it still cannot "Sort by test1" ...

[amitchandak]

@YunJ , Sorting on Matrix once the column is used will happen on row grand total of the measure. In case measure is not visible, check its data type

[YunJ]

Hi @amitchandak . It is numeric. 

[YunJ]

Hi @Pragati11 @amitchandak 

I think I found the reason.

Here you can see when slicer Category=Skincare, the Total has value.

However, when Category=Makeup, the Total does not have value, so it didn't sort. 

But why Makeup does not have Total number? I wrote the same measure for them...

[Pragati11]

Hi @YunJ ,

Looks like something is wrong on your Matrix TOTALS. Check them again. Also, check in your data is the matrix is showing right numbers.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[YunJ]

Hi @Pragati11 

Thanks for your reply.

I checked the test1. The numbers shown in the matrix are correct. So it is really weird why only Makeup does not have the Total. Skincare has the Total...

[v-yiruan-msft · Accepted solution]

Hi @YunJ ,

Please try to create another measure with the below formula based on the measure "test1" , then drag it onto the matrix to replace measure “test1”:

Measure =
SUMX (
    VALUES ( 'Store'[Store_eng] ),
    SUMX ( VALUES ( 'Sub-Category'[Sub-Category] ), [test1] )
)

Best Regards

Rena

[YunJ]

Hi @v-yiruan-msft 

Thanks a lot for your solution.

May I know why the Total has value when selecting slicer Category=Skincare?

Thanks for your time.