Dynamically Align Company Order Between Cluster Chart and Matrix Based on Quarter Slicer

[MichaelZang]

Hi everyone,

I hope you can help me with this issue - thanks in advance! 😊

Context: You can see from the attached image below that I have:

Cluster Chart

• x-axis: company_name
• y-axis: USG
• legend: period_label

Matrix

• Row: Metric
• Column: company_name
• Value: Metric Value

Problem: I need to ensure that the Cluster Chart is sorted in descending order of USG AND that the Matrix dynamically follows this same order for company names based on the selected period and year in the slicer.

For example:

• Q4 2022: Cluster Chart company order is A, B, C, D -> Matrix company order should be A, B, C, D
• Q1 2023: Cluster Chart company order is B, D, A, C -> Matrix company order should be B, D, A, C

Attempts so far (unsuccessful):

• Tried both horizontal and vertical charts but couldn't achieve the desired sorting
• Attempted to create a hierarchy sorting by USG (UPG and UVG are under USG), but it did not work

Looking for a solution to make the Matrix column/row dynamically follow the Cluster Chart's USG-based order. 😓

Note: I created the company_order_index to align the Cluster Chart and Matrix, but it's only a temporary fix since the company order remains static, which is not the intended outcome.

What I need:

[danextian · Accepted solution]

Hi @MichaelZang 

Matrix values are sorted based on the total column so the total must return the value for USG

UPG_UVG_UOM_Values = 
VAR _USG =
    CALCULATE (
        SUM ( fact_financials_unpivoted_percentage[Metric Value] ),
        fact_financials_unpivoted_percentage[Metric] = "USG"
    )
RETURN
    IF (
        NOT ( HASONEVALUE ( fact_financials_unpivoted_percentage[Metric] ) ),
        _USG,
        CALCULATE (
            SELECTEDVALUE ( fact_financials_unpivoted_percentage[Metric Value] ),
            FILTER ( ALL ( dim_region ), dim_region[region_name] = "Total Company-Wise" )
        )
    )

[MichaelZang]

Hi @danextian 

Thank you for your quick response. I really appreciate your help on this issue

[MichaelZang]

DATA + PBIX FILE: HERE