Dax for total row optimization

[jaryszek]

hi Guys,

this is my DAX:

EffectivePrice Percentage = 
IF(ISINSCOPE(Fct_EA_AmortizedCosts[Discountability]) || ISINSCOPE(Fct_EA_AmortizedCosts[DiscountPlanName]) || ISINSCOPE(Fct_EA_AmortizedCosts[SubscriptionName]) || ISINSCOPE(Dim_EA_AmortizedCosts_Resources[ResourceType]),
    DIVIDE(
    SUMX(
        Fct_EA_AmortizedCosts,
        ('Fct_EA_AmortizedCosts'[EffectivePrice]) * ('Fct_EA_AmortizedCosts'[WeightInBillingCurrency])
    ),
    SUM('Fct_EA_AmortizedCosts'[WeightInBillingCurrency])
    ) / 100, 
    BLANK()
)

as you can see I am adding ISINSCOPE all the time when new page with new columns in matrix arrived in order to not show total row. 

Is there a possibility to optimize this DAX to be more generic?

Best,
Jacek

[v-pnaroju-msft · Accepted solution]

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.

[bhanu_gautam]

@jaryszek If you want to show the measure only when a single value is selected for [Discountability], [DiscountPlanName], [SubscriptionName], or [ResourceType], you can do:

DAX
EffectivePrice Percentage =
IF(
HASONEVALUE(Fct_EA_AmortizedCosts[Discountability])
|| HASONEVALUE(Fct_EA_AmortizedCosts[DiscountPlanName])
|| HASONEVALUE(Fct_EA_AmortizedCosts[SubscriptionName])
|| HASONEVALUE(Dim_EA_AmortizedCosts_Resources[ResourceType]),
DIVIDE(
SUMX(
Fct_EA_AmortizedCosts,
Fct_EA_AmortizedCosts[EffectivePrice] * Fct_EA_AmortizedCosts[WeightInBillingCurrency]
),
SUM(Fct_EA_AmortizedCosts[WeightInBillingCurrency])
) / 100,
BLANK()
)

[jaryszek]

thanks, 

but still i need to provide field names, it is not generic.