Forum Discussion
Helper IDAX Measure: Incorrect Total of PBI vs Excel export
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!
Super Userdatajunkie_29 Try using
DAX
Budget Balance =
IF(
[Total YTD Budget] = 0,
[OpenAging] * -1,
[Total YTD Budget] - SUMX('Transaction Detail', 'Transaction Detail'[Participation/Received])
)
Helper IHi 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
Hi Anonymous, I was able to solve the problem and have posted the solution. Thanks!