cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PeterRLC Frequent Visitor
Frequent Visitor

Trial Balance Total Incorrect

Trial Balance.PNGTotals should be 1,177,009.73

 

I want the total of my Trial Balance to equal the displayed line values.  The problem is that the displayed line values are sorted to Dr or Cr based on their SUMMARY level sign, positive is Dr, negative is Cr.  I have tried 4 different calculations and the best is this one but the part in red is not calculating the total correctly:

 

TB. Cr S1 = IF (
    HASONEVALUE (xFMS_iFAMS_Base_Journal_FY19[FMS-iFAMS GL]),
        if(sum(xFMS_iFAMS_Base_Journal_FY19[Amount])<0, -sum(xFMS_iFAMS_Base_Journal_FY19[Amount])),
            sumx( all(xFMS_iFAMS_Base_Journal_FY19[FMS-iFAMS GL]),-sum(xFMS_iFAMS_Base_Journal_FY19[Amount]))       
)
1 ACCEPTED SOLUTION

Accepted Solutions
PeterRLC Frequent Visitor
Frequent Visitor

Re: Trial Balance Total Incorrect

Solved it! Thanks to Measure-Totals-The-Final-Word Measure total basics.  The correct calculation shows up circled in blue below.  Any problems with this solution?

 

TB. Dr Var =
VAR __Table = SUMMARIZE( xFMS_iFAMS_Base_Journal_FY19, [FMS-iFAMS GL], "TB Total", [TB. Dr S1])

Return
SWITCH(True(),
    HASONEVALUE( xFMS_iFAMS_Base_Journal_FY19[FMS-iFAMS GL]),[TB. Dr S1],
    sumx(__Table, [TB Total])
)TB Calculation problem Fixed.PNG

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Trial Balance Total Incorrect

Hi @PeterRLC ,

From the information you provided, I could only get the output like this.

Capture.PNG

 

If it is convenient, could you share your data sample which could reproduce the scenario as table format and your desired output so that we could give further advice on it?

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PeterRLC Frequent Visitor
Frequent Visitor

Re: Trial Balance Total Incorrect

TB Calculation problem.PNG

 

Yes, here is the original amount column.  I want to sort negatives to the credit column and positives to the debit column.

PeterRLC Frequent Visitor
Frequent Visitor

Re: Trial Balance Total Incorrect

Solved it! Thanks to Measure-Totals-The-Final-Word Measure total basics.  The correct calculation shows up circled in blue below.  Any problems with this solution?

 

TB. Dr Var =
VAR __Table = SUMMARIZE( xFMS_iFAMS_Base_Journal_FY19, [FMS-iFAMS GL], "TB Total", [TB. Dr S1])

Return
SWITCH(True(),
    HASONEVALUE( xFMS_iFAMS_Base_Journal_FY19[FMS-iFAMS GL]),[TB. Dr S1],
    sumx(__Table, [TB Total])
)TB Calculation problem Fixed.PNG

View solution in original post

Highlighted
PeterRLC Frequent Visitor
Frequent Visitor

Re: Trial Balance Total Incorrect

Is it possibly a memory hog?  And what would the solution be for that?

 

Also tried: (Calculates correctly but also runs out of memory)

TB. Ending Balance Dr =
if(
HASONEVALUE( xFMS_iFAMS_Base_Journal_FY19[FMS-iFAMS GL]),[TB. Dr S1],
sumx(SUMMARIZE( xFMS_iFAMS_Base_Journal_FY19, [FMS-iFAMS GL], "TB Total", [TB. Dr S1]), [TB Total])
)

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors