Incorrect results for Divide function

1. Verify Relationships:

   • Ensure there's a valid relationship between '2025 Baseline Forecast' and 'Last Month Positions'.
   • If these tables are not directly related, the SUM calculations might not be in the same filter context.

2. Test Individual Components:

   • Create separate measures to confirm each component is returning expected results:
     DAX

     Copy code

     TotalExpense = SUM('2025 Baseline Forecast'[Annual Expense]) TotalBookValue = SUM('Last Month Positions'[Book Value])
   • Use these measures in a table visual alongside your dimensions to ensure they calculate correctly.

3. Validate Filter Context:

   • If your data uses different dimensions (e.g., time, region, category), ensure both tables are being filtered correctly by the same dimensions.

4. Row-Level Aggregation Check:

   • Ensure the calculation doesn’t suffer from granularity mismatch. For example, if Book Value is stored at a more granular level (e.g., daily) than Annual Expense (e.g., yearly), aggregations might not align correctly.

Adjusted Formula:

If your data model has no issues and you simply need the correct context for the division:

Additional Scenarios:

• If Tables Are Not Related: Use CROSSFILTER or ALL to manipulate filter context:

  DAX

  Expense Ratio = DIVIDE( CALCULATE(SUM('2025 Baseline Forecast'[Annual Expense]), ALL('Last Month Positions')), CALCULATE(SUM('Last Month Positions'[Book Value]), ALL('2025 Baseline Forecast')), 0 )

• If You Need a Weighted Average: Use a more complex formula to handle weighting:

  DAX

  Expense Ratio = DIVIDE( SUMX( 'YourCommonDimensionTable', CALCULATE(SUM('2025 Baseline Forecast'[Annual Expense])) ), SUMX( 'YourCommonDimensionTable', CALCULATE(SUM('Last Month Positions'[Book Value])) ), 0 )

Visual Troubleshooting:

• Add the Expense Ratio measure to a matrix visual with relevant dimension columns (e.g., year, category) to confirm results at different levels of granularity.

If the issue persists, share details about the data relationships, granularity, or any specific filters that may affect calculations.