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

[Danniel]

Hi everyone,

In Power BI, I have this measure:

Master Monto Retención USD = 
SUMX(Transferencias_Master_excel, Transferencias_Master_excel[Monto Nota de Credito]) / SUM(TablaTipoCambio[Cambio]) + 
SUMX(Transferencias_Master, Transferencias_Master[Monto Nota de Credito]) / SUM(TablaTipoCambio[Cambio])

The Transferencias_Master_excel table contains many records with different transaction dates (Fecha Transferencia), while TablaTipoCambio has a row per month indicating the applicable exchange rate (date). Some months are loaded with blank data in the exchange rate column, due to they are future months so I don't have data yet.

This measure works well for monthly data, but it doesn’t produce correct results when I try to view annualized data — specifically, the total in a matrix visualization. Instead of summing the monthly values, it seems to be averaging or aggregating incorrectly.

I have a dim_date table connected to both data tables via their date fields. How can I modify my measure so that the total reflects the sum of all months (i.e., the correct annual total), rather than an inaccurate aggregate?

Thanks in advance for any guidance!

[burakkaragoz · Accepted solution]

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.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Show the problem and expected result there.  If possible, please try to have the table/column names in English.