Issue with Total Calculation in Power BI — Sum vs. Average for Annualized Data

Hi Danniel ,

You're hitting a classic issue with division in measures. When Power BI aggregates your measure at the year level, it's recalculating the entire formula instead of summing the monthly results.

What's happening:

• Monthly level: Your measure calculates correctly
• Annual level: Power BI runs the formula again across all months, and SUM(TablaTipoCambio[Cambio]) is summing all exchange rates, not using monthly ones

The fix - calculate monthly first, then sum:

Master Monto Retención USD = 
SUMX(
    VALUES(dim_date[Month-Year]), -- or whatever your month identifier is
    VAR CurrentMonth = dim_date[Month-Year]
    VAR MonthlyExcel = CALCULATE(SUM(Transferencias_Master_excel[Monto Nota de Credito]), dim_date[Month-Year] = CurrentMonth)
    VAR MonthlyMaster = CALCULATE(SUM(Transferencias_Master[Monto Nota de Credito]), dim_date[Month-Year] = CurrentMonth)
    VAR MonthlyRate = CALCULATE(SUM(TablaTipoCambio[Cambio]), dim_date[Month-Year] = CurrentMonth)
    RETURN
    IF(MonthlyRate > 0, (MonthlyExcel + MonthlyMaster) / MonthlyRate, 0)
)

Alternative approach - iterator pattern:

Master Monto Retención USD = 
SUMX(
    SUMMARIZE(
        FILTER(TablaTipoCambio, TablaTipoCambio[Cambio] <> BLANK()),
        TablaTipoCambio[Date]
    ),
    VAR CurrentDate = TablaTipoCambio[Date]
    VAR DailyRate = CALCULATE(SUM(TablaTipoCambio[Cambio]))
    VAR DailyExcel = CALCULATE(SUM(Transferencias_Master_excel[Monto Nota de Credito]))
    VAR DailyMaster = CALCULATE(SUM(Transferencias_Master[Monto Nota de Credito]))
    RETURN DIVIDE(DailyExcel + DailyMaster, DailyRate, 0)
)

This iterates through each date with exchange rate data and calculates the conversion, then sums everything up properly.

The key is forcing Power BI to do the division at the granular level first, then sum those results.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.