Forum Discussion
Helper IDAX Measure: Incorrect Total of PBI vs Excel export
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:
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!
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!
5 Replies
- bhanu_gautam
Super User
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_29Hi 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.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
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!
