topic Re: DAX Measure: Incorrect Total of PBI vs Excel export in DAX Commands and Tips

DAX Measure: Incorrect Total of PBI vs Excel export

datajunkie_29 — Wed, 12 Feb 2025 22:37:30 GMT

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!

Re: DAX Measure: Incorrect Total of PBI vs Excel export

bhanu_gautam — Thu, 13 Feb 2025 03:37:04 GMT

datajunkie_29 Try using

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

Re: DAX Measure: Incorrect Total of PBI vs Excel export

datajunkie_29 — Thu, 13 Feb 2025 15:27:21 GMT

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.

Re: DAX Measure: Incorrect Total of PBI vs Excel export

Anonymous — Mon, 17 Feb 2025 08:44:03 GMT

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.

Re: DAX Measure: Incorrect Total of PBI vs Excel export

datajunkie_29 — Mon, 17 Feb 2025 17:50:12 GMT

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!

Re: DAX Measure: Incorrect Total of PBI vs Excel export

datajunkie_29 — Mon, 17 Feb 2025 17:52:01 GMT

Hi Anonymous, I was able to solve the problem and have posted the solution. Thanks!