Force Matrix Table Subtotal at level 1

[NelsonPay]

Hello I want to force the subtotal for a Matrix table with two levels In level 1 the subtotal is 0 but in level 2 there are positive values. This is the original formula: 

VAR total_positives=SUM('Total Sells'[Oversell >125%])
RETURN IF(total_positives>0,total_positives,0)

Level 1 is Product and Level 2 is Country for Product the subtotal is 0 but when you expand you find that in Country there is positive results. Also the idea of the formula is to make a normal sum but filter the result if it is a positive result show me that value if it negative show me 0. What can I do to make that the values appearing in country appears in the subtotal of product. 

[Kedar_Pande]

@NelsonPay 

Corrected Measure =
VAR TotalPositives = SUM('Total Sells'[Oversell >125%])
VAR IsSubtotal = ISINSCOPE('Table'[Country]) && NOT(ISINSCOPE('Table'[Product]))
RETURN
IF(
IsSubtotal,
TotalPositives,
IF(TotalPositives > 0, TotalPositives, 0)
)

If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[v-ssriganesh]

Hello @NelsonPay, 

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[v-ssriganesh]

Hello @NelsonPay,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh]

Hello @NelsonPay,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hi @NelsonPay,
Thank you for reaching out to the Microsoft fabric community forum.

I’ve reproduced your scenario in Power BI Desktop using sample data with Product → Country hierarchy and the Oversell >125% column. Initially, I observed the same issue you described. the Product-level subtotal was showing 0 even though some Countries had positive values.

To resolve this, you need to adjust the DAX measure so that the “positive check” happens at the Country level, and then these results roll up correctly to the Product subtotal.

Here’s the corrected measure:

Total Positives =

SUMX (

    VALUES ( 'Total Sells'[Country] ),

    VAR total_positives =

        CALCULATE ( SUM ( 'Total Sells'[Oversell >125%] ) )

    RETURN IF ( total_positives > 0, total_positives, 0 )

)

For your reference, I’ve attached a sample .pbix file.

 
Best regards,
Ganesh Singamshetty.

[lbendlin]

Note that you posted this in the Power Query section - better suited for a DAX related section.

Use the ISINSCOPE function to sense where in the matrix you are and return BLANK() for all levels where you don't want to see totals.