Need Urgent HELP! on DAX

[Newbie12345]

Dear All,

I want to show the sub total in a separate column in the matrix visual as shown in the screenshot. 
Population is a measure, not a direct column from the database. 
NOTE: Country is from different table and State is from different table. 
           Same State Name will be there in different countries

Can someone please help me around with this?

Thank you.

[v-nuoc-msft · Accepted solution]

Hi @Newbie12345 

@gmsamborn Thank you very much for your prompt reply, and here allow me to make some comments of my own.

Power BI does not inherently support displaying subtotals in separate columns within the same matrix. However, you can create a separate visual to show totals.

For your question, here is the method I provided:

Check to see if there is a relationship between the "Country" and "State" tables in the model. If not, make sure there is a public key that links these tables directly or indirectly.

Since you mentioned that "Population" is a metric and not a direct column, you will need to create a metric using DAX to correctly calculate subtotals for different states and countries.

Sub Total = 
SUMX(
    FILTER(
        'Country / State', 
        'Country / State'[Country] = MAX('Country / State'[Country])
    ), 
    'Country / State'[Measure Population]
)

You can create a card for displaying Sub Total.

Also used in conjunction with the slicer for showing population totals under different countries.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lukiz84]

SubTotal = CALCULATE([Population], ALL(YourStateTable))

[Newbie12345]

Dear @lukiz84 ,

Thanks for your response! 
The problem here is, I have same state name in other countries too, so we can not use ALL, 
I tries ALLEXCEP as well but since it is a matrix, it is giving row level value along with subtotal. 
Want I am trying to get here is, only Subtotal like a merged single value (as shown in the screenshot above)

[gmsamborn]

Hi @Newbie12345 

I'm not sure you can make a matrix column appear to be a Excel merged column.

Regarding your measure, would this help?

Subtotal = 
    IF(
        NOT HASONEVALUE( 'Table'[State] ),
        CALCULATE(
            SUM( 'Table'[Population] ),
            REMOVEFILTERS( 'Table'[State] )
        )
    )

I hope this helps.