Sorting Complex Matrix visual by Column subtotals

[TomyVisual]

Dear Experts,
I am facing specific problem.. I have found out that Power BI has the problem to sort matrix table based on column Subtotal when My matrix table is quiet complex.. It contains 3 lvl hierarchy in row Section. Only date in columns and 1 big complex measure which computes various different calculations from different tables based on 3 slicers arround it.. I have been asked by my management if I can manage to sort descending every single view on every single aspect in matrix tbale.. I thought it will be built in functionality but unfortunately is not.. Going deeper and deeper in this request I am finding out that its very very complex. Appreciate any hints, tips, advices.. any help is welcomed. 

[TomyVisual]

Hello Guys, 
Thanks for interacting, unfortunaletly I can not include data set due to confidential data But I have a good news and bad news.. Good neews is that I have found out why it is not sorting.. and It is caused by formating Issue.. My complex measure containing multiple other measures which needs to be formated differently.. Some as Whole numebr, some as Percentage and others as decimal numebrs with 2 decimal precision.. Unfortunately I have used Format fucntion to meet this criteria however as we know format function formt text and thats why unexpected results has occured.. Do you guys have Idea how can I Format measure as whole number, percentage, decimal?

[Anonymous · Accepted solution]

@TomyVisual  You can try the new dynamic format feature. This is exactly what you need! Create dynamic format strings for measures in Power BI Desktop - Power BI | Microsoft Learn

Here is a blog that may be more helpful than the official document: Introducing dynamic format strings for DAX measures - SQLBI

Best Regards,
Jing

[TomyVisual]

I have checked whole tutorial but I have no idea How to implemet it in my case

[Anonymous]

I agree this is quite a complex measure...

My idea is that you may try creating a DAX formula similar to your measure. In this formatting DAX formula, where it originally have a calculation formula or a measure, replace it with the format string you want for that calculation. Just like this example

    SWITCH (
        TRUE (),
        CurrentValue <= 1E3, "#,0.00 K",
        CurrentValue <= 1E6, "#,0,.00 K",
        CurrentValue <= 1E9, "#,0,,.00 M",
        CurrentValue <= 1E12, "#,0,,,.00 G"
    )

Regards,
Jing

[Anonymous]

Hi @TomyVisual 

I made a test with a simple SUM calculation for three hierarchy-level rows. The decending sorting seems correct. Do you mean the sorting in the child hierarchy doesn't work correctly when the calculation is very very complex?

Best Regards,
Jing

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...