Measure total incorrect in Table

Hi gmasta1129 

In Power BI, it’s common to encounter issues where the total row of a measure does not reflect the expected sum of individual rows, and this is due to how DAX measures are evaluated in the context of totals. Your measure, FundStrat% = [% of Eligible Assets] * CALCULATE(SUM(enav_raw_input[Invest. Strat - Risk Parameter Score])), works correctly on a row-by-row basis because the row context allows DAX to evaluate the values of each row individually. However, when the "Total" is calculated, DAX evaluates the measure in the context of the total group, meaning it no longer computes each row individually and sums them, but rather evaluates the expression once over the entire filtered dataset. This can lead to unexpected results—especially when your measure involves row-level multiplication, like multiplying one measure by a row-dependent sum.

 

Additionally, the presence of filters like "Run Date" and "Facility Code" affects the context in which both the rows and the total are calculated. These filters may restrict the dataset correctly, but they don’t change the fact that totals are computed in a different evaluation context.

 

To get an accurate total, you may need to rewrite the measure to explicitly aggregate the row-level results. For example, using SUMX, which iterates over each row in a table and performs the calculation per row before summing, can fix this:

FundStrat% = SUMX(
    VALUES(enav_raw_input[SomeGroupingColumn]),
    [% of Eligible Assets] * CALCULATE(SUM(enav_raw_input[Invest. Strat - Risk Parameter Score]))
)

Replace SomeGroupingColumn with the column that defines the row-level granularity of your table. This approach forces DAX to mimic row-by-row evaluation even at the total level, providing the accurate sum you expect.