Issue on Sorting in Matrix Using Formatted Values

[cristianpark]

Hi guys, I'm having an issue when I try to sort by values in a Matrix when I have a formatted measure as values.

You can see in image above that I have $84.0k after $108.4k and everything is messed up because the resulting String type of measure

Measure definition is like this

Metric Selected (Formatted) = 
IF( ISFILTERED('Metrics'[Slicer]),
    SWITCH(LASTNONBLANK('Metrics'[Slicer], ""),
        "KPI 1", FORMAT([KPI 1 Value], "#,##0"),
        "KPI 2", FORMAT([KPI 2 Value], "#,##0.0%")
    ),
    FORMAT([KPI 1 Value], "#,##0")
)

That means that each KPI has its own formatting style so I can't have format options selected on Modeling tab of menu.

I have an unformatted version of measure and that's the one I use in charts but since measures are not sortable by other measures (like columns are) I'm clueless on what to do.

Do you know if there's a way to properly sort values with this setup?
   

[v-juanli-msft]

Hi @cristianpark 

A workaround is to create a rank measure

Measure len = LEN([Metric Selected (Formatted)])

Measure rank = RANKX (
    ALL ( 'Table1' ),
    RANKX ( ALL ( Table1 ), [Measure len],, ASC )
        + DIVIDE (
            RANKX ( ALL ( Table1 ), [Metric Selected (Formatted)],, ASC ),
            COUNTROWS ( ALL ( Table1 ) ) + 1
        ),,ASC,Dense)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cristianpark]

Thanks for your reply Maggie, an additional issue in my case is that is not a 2 column table but a n time periods based matrix so I have 18 columns for months and then I'm displaying values for selected kpi.

So I don't get to hide the additional measure by resizing column width to 0

[v-juanli-msft]

Hi @cristianpark 

Since ther are many months, which Formatted Values do you want to sort by?

Formatted Values of Month1? month 2? sum value for all months?

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cristianpark]

Hi Maggie,

I'm not looking for a particular order by month, actually if you put a Top N (5 for example) filter in Matrix using unformatted measure, you'll get Top 5 based on first month available from left to right.

That behaviour will be acceptable in this case I have but if is there any way to pick a particular month for sorting it will be useful as well.

Thanks