Dax for total row optimization

Thankyou, bhanu_gautam, for your response.

Hi jaryszek,

We appreciate your inquiry submitted through the Microsoft Fabric Community Forum.

Based on my understanding, DAX currently does not provide a built-in method to automatically detect which fields are used in a Matrix visual or to determine whether a cell represents a total or a subtotal. Functions such as ISINSCOPE(), HASONEVALUE(), and HASONEFILTER() operate on a per column basis and therefore require explicit column references. This behaviour is by design and does not indicate a product defect.

DAX cannot generically detect totals without specifying columns. The helper measure pattern below is a best practice approach for long term maintainability and performance.

1. To simplify maintenance and optimise your measure, you can centralise all your ISINSCOPE() checks in a single helper measure or variable instead of repeating them:

-- Helper measure (optional)
IsRowContext =
OR(
ISINSCOPE(Fct_EA_AmortizedCosts[Discountability]),
ISINSCOPE(Fct_EA_AmortizedCosts[DiscountPlanName]),
ISINSCOPE(Fct_EA_AmortizedCosts[SubscriptionName]),
ISINSCOPE(Dim_EA_AmortizedCosts_Resources[ResourceType])
)

-- Final measure
EffectivePrice Percentage =
VAR Numerator =
SUMX(
Fct_EA_AmortizedCosts,
Fct_EA_AmortizedCosts[EffectivePrice] * Fct_EA_AmortizedCosts[WeightInBillingCurrency]
)
VAR Denominator = SUM(Fct_EA_AmortizedCosts[WeightInBillingCurrency])
VAR WeightedAvg = DIVIDE(Numerator, Denominator) / 100
RETURN
IF([IsRowContext], WeightedAvg, BLANK())

This approach ensures that you maintain only a single location (IsRowContext) if you add new columns later.

Alternatively, to hide totals visually, go to the Format pane, select Subtotals, and turn off Row or Column totals.

We hope that the information provided will help to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.