Moving average mismatch

The problem with averages is that they are not additive. If you slice or apply filters use can't use a precalculated average to generate a new average. You should really use a measure so that this will be dynamically calculated in respect to filter etc that are applied to the report

Hi sk30 ,

Thank you for reaching out to the Microsoft Community Forum.

1. Query Folding Issues: Power BI sometimes rewrites SQL queries during query folding (even in Import mode), especially if you're applying any steps in Power Query. This can alter how the moving average is computed especially if ROWNUM, PARTITION BY, or ORDER BY is involved in your SQL.

Solution: Right-click the step in Power Query → “View Native Query”. If it's greyed out or the query doesn't match your original SQL, then folding is being broken or altered.

2. Truncation or Rounding Differences: Power BI (especially Power Query) and TOAD may handle numeric precision or rounding slightly differently, depending on the column types (e.g., NUMBER(18,5)).

Solution: Are the mismatched values off by a small decimal margin (like 0.0001)? If so, this could be due to implicit type casting or rounding at load time.

3. Sort Order is Not Respected: A moving average depends on the order of rows. If Power BI is pulling in the correct data but the sort order isn't preserved exactly as in your SQL (for example, if no ORDER BY clause is retained during folding), the rolling logic could be thrown off.

Solution: Try outputting the exact row order into a temp table in SQL and pull that into Power BI see if it then matches TOAD.

4. Caching or Partial Load: Power BI sometimes caches preview data or doesn't show full datasets in the Power Query Editor (especially in Import mode). You might be seeing outdated or partial previews.

Solution: Click "Close & Apply" and then look at the data in the Data view (not the Power Query Editor), or add a simple table visual in Report view to confirm the final loaded values.

5. Data Type Mismatch: If Power BI interprets the column as text or a different numeric type, it might affect how the values are displayed or calculated even if no transformations are applied.

Solution: In Power Query, make sure the column type is explicitly set to Decimal Number or the correct numeric type.

Note: Try creating a SQL view in Oracle with the full logic, then point Power BI to that view. That way, folding and transformations are guaranteed not to interfere. Export the SQL query results to CSV from TOAD and import that same file into Power BI to test value consistency. Compare row counts and moving average values for a specific key or partition sometimes the window frame logic differs subtly across platforms.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you