DAX Measure: Incorrect Total of PBI vs Excel export

[datajunkie_29]

Hello Power BI Community,

I'm facing an issue with my DAX measure Budget Balance, where the totals in Power BI visual don't match when exported the results to Excel. The row-level calculations seem fine, but the total is incorrect.

Here’s my current measure trace:

• Due = [ParticipationAmt] - [ReceivedAmt]
• OpenAging = CALCULATE([Due], Event[Event Age Group] <> "Paid/Completed", REMOVEFILTERS(Event[Event Date]), Event[Event Age] > 0)
• FlatRatesTotal = IF(ISINSCOPE('Branch'[FPA Name]) || ISINSCOPE('Branch'[RM Name]) || ISINSCOPE('Branch'[Branch]), BLANK(),
  SUM('Flat Rates'[Flat Rates]))
• Total YTD Budget = [ActualBudgetTotal] + [FlatRatesTotal]
• Budget Balance = IF([Total YTD Budget] = 0, [OpenAging]*-1, [Total YTD Budget] - SUM('Transaction Detail'[Participation/Received]))

Calculated Column:

Participation/Received = IF(LOOKUPVALUE(Event[Event Status], Event[Event ID], 'Transaction Detail'[EVENTID])="Closed", [Received $], [Participation $])

The issue: The total for Budget Balance does not align with Excel (PBI totals are lower compared to Excel) I suspect the issue may be with SUM vs. iterating functions like SUMX or how totals are aggregating at different levels.

Can anyone help identify what might be causing the incorrect total and how to fix it? Appreciate any insights!

Thanks in advance!

[datajunkie_29 · Accepted solution]

Hello,
I’ve found a solution that correctly calculates the Budget Balance total by using SUMMARIZE along with SUMX to iterate over the summarized table & ensure accurate aggregation.

DAX Measure:

VAR Result =
SUMMARIZE(
'Budgeted Suppliers',
'Budgeted Suppliers'[BUDGETID],
"SupplierBalance",
IF([Total YTD Budget] = 0, [OpenAging] * -1, [Total YTD Budget] - SUM('Transaction Detail'[Participation/Received]))
)

RETURN
SUMX(Result, [SupplierBalance])

This approach prevents Power BI from applying the calculation at the total level incorrectly and ensures the sum of row-level results matches the expected total.

Thank you! Heope this helps!

[bhanu_gautam]

@datajunkie_29 Try using

DAX
Budget Balance =
IF(
[Total YTD Budget] = 0,
[OpenAging] * -1,
[Total YTD Budget] - SUMX('Transaction Detail', 'Transaction Detail'[Participation/Received])
)

[datajunkie_29]

Hi @bhanu_gautam ,
Thank you for your response, I've tried using SUMX, which ensures the correct row-level results, but the grand total still doesn’t align.

[v-jianpeng-msft]

Hi, @datajunkie_29 

Can you provide some sample data or sample PBIX files that don't contain privacy so that we can analyze them?

If you have already solved this problem, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. 

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[datajunkie_29]

Hi @v-jianpeng-msft, I was able to solve the problem and have posted the solution. Thanks!