Display Subtotal Column Based on Another Column's Values in Power BI

[MichaelZang]

Hi everyone,

Sorry for re-posting but I'm pretty desperate about this issue 😓

I hope you can help me with this issue. I appreciate it!!! 

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

Cluster Chart

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

Matrix

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

Problem: I'm not sure how to display the Subtotal column in the matrix so that it reflects values based on the USG column.

What I need: I need 'Column Subtotals' to correctly display values from the USG column so I can sort them in descending order to align with the cluster chart on the right.

Attempt: I have rewritten the DAX formula multiple times, but I haven’t been able to fix the issue 

PBIX FILE: HERE

[sroy_16]

Hello @MichaelZang 

Try using thius measuire and see if it fulfils your requirement or not.

Metric Value with USG Subtotal =
SUMX(
VALUES(YourTable[company_name]),
CALCULATE(SUM(YourTable[Metric Value]), YourTable[USG])
)

I hope my solution helps! If it works for you and resolves the issue you're facing, please consider marking it as the solution. This will make it easier for others who encounter the same problem to find the answer and save them some time.

If you're happy with the solution, feel free to provide some Kudos

[SamWiseOwl · Accepted solution]

Hi @MichaelZang 

I am probably misunderstanding but why not reference your existing measure in the total?

    IF(

       not( ISINSCOPE(financial_percentage[Metric]))

       ,      [USG_Global]

        ,           CALCULATE (

            SELECTEDVALUE ( financial_percentage[Metric Value] ),

            FILTER ( ALL ( dim_region ), dim_region[region_name] = "Global" )

        )

    )

[MichaelZang]

Hi @SamWiseOwl 

Thank you for your prompt answer 😊 It works perfectly 😊 I've struggled with it for the last few days

I really appreciate your help 😁