How to create a line to balance amounts (charts of accounts)

[KevinSV]

Hello everyone ! 
I need your help about a project on Power BI Desktop. 
First of all, I extract my data from SQL Server and the table I need is PCA). And all the columns of my table are : ANALYT ; CLIENT ; CODEJOUR ; CREDITS ; DEBITS ; DTECRIT ; ETABL ; EXERCICE ; ID_SOCIETE ; LIB2 ; LIGNE ; NUM_COMPT ; NUM_FAC ; PIECE ; REFGESTION ; SECTION ; SOCIETE.
So after the cleaning, I put some slicers to filter with DTECRIT, SOCIETE. With the filters, I obtain a visual table where I put all the columns i need : 

As you can see i obtain some amount in credits but nothing in Debits. I want to create a line (on the top of the table) where the TCOMPTE number will begin with 487 and add 0 in function of the length of the other TCOMPTE (in the screen the length is 6 so i want 3 more 0 on my 487) and a balance for the amount (in this exemple, I have 40 061.50 in DCREDITS and 0 in MDEBITS so I want 40 061.50 in MDEBITS). So, I want to put a balance in the column which had the less amount (for example if I have 1000 in Credits and 300 in Debits, SO on the top line with the TCOMPTE beginning with 487, I will have 700 in Debits (the calcul is the difference between the sum of credits and debits when credits > debits or inverse the difference between the sum of debits and credits when debits > credits). 
Is that possible ? Thank you for your help 🙂 ! 

[Anonymous · Accepted solution]

Hi @KevinSV ,

First create a calculated column that generates a new TCOMPTE number that starts with “487” and adds the appropriate number of “0's” based on the maximum length of the existing TCOMPTE number.

TCOMPTE_Balance =
"487"
    & REPT ( "0", LEN ( MAXX ( ALL ( PCA ), PCA[TCOMPTE] ) ) - 3 )

Then create the following calculation table that contains the data from the original table PCA and a new row for balancing amounts.

BalanceTable = 
UNION(
    SELECTCOLUMNS(
        PCA,
        "TCOMPTE", PCA[TCOMPTE],
        "MDEBITS", PCA[MDEBITS],
        "DCREDITS", PCA[DCREDITS],
        "DTECRIT", PCA[DTECRIT]
    ),
    ROW(
        "TCOMPTE",  MAX(PCA[TCOMPTE_Balance]),
        "MDEBITS", IF(SUM(PCA[DCREDITS]) > SUM(PCA[MDEBITS]), [BalanceAmount], 0),
        "DCREDITS", IF(SUM(PCA[MDEBITS]) > SUM(PCA[DCREDITS]), [BalanceAmount], 0),
        "DTECRIT", MAX(PCA[DTECRIT])
    )
)

Best Regards,
Adamk Kong

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

[KevinSV]

Hi @Anonymous, 
So I have a problem as you can see on the screenshot, I put some slicers for filter with a society and a date (this columns are on the PCA table) : 

So the table i have looks like this : 

But when i put your columns (i related your table with the PCA by creating a column which calculate is "Fictive = 1"), I have few columns that don't exists on the society i filter : 

And the amount is totally different, in my exemple i hope to get on my first line (where the TCOMPTE will begin with 487) an MDEBITS amount of 28 156.22 but i got this results : 

How can I fix it ? I thought about a selectedvalue but i'm not sure. 
Thanks again ! 

[KevinSV]

Hi everyone,
I try the code of @Anonymous for my project and it works (thank you again).
The problem I have is the amount on the line of 487 is here only without filter so the total. But when I want to filter with a Society, with a date and a "CODEJOUR", the line which equilibrate the amount disapear. 
Here is the measure, i use : 

BalanceAmount =

VAR TotalCredits = CALCULATE(SUM(PCA[CREDITS]))

VAR TotalDebits = CALCULATE(SUM(PCA[DEBITS]))

RETURN

IF(TotalCredits > TotalDebits; TotalCredits - TotalDebits; TotalDebits - TotalCredits)

And the table i create : 

BalanceTable =

VAR BalanceAmountValue = [BalanceAmount]  

RETURN

UNION(

    SELECTCOLUMNS(

        PCA;

        "LIBELLE"; PCA[LIB2];

        "CODEJOUR"; PCA[CODEJOUR];

        "SOCIETE"; PCA[SOCIETE];

        "TCOMPTE"; PCA[NUM_COMPT];

        "MDEBITS"; PCA[DEBITS];

        "DCREDITS"; PCA[CREDITS];

        "DTECRIT"; PCA[DTECRIT]

    );

    ROW(

        "LIBELLE"; "";

        "CODEJOUR"; SELECTEDVALUE(PCA[CODEJOUR]);

        "SOCIETE"; SELECTEDVALUE(PCA[SOCIETE]);

        "TCOMPTE"; "487" & REPT("0"; LEN(MAXX(ALL(PCA); PCA[NUM_COMPT])) - 3);

        "MDEBITS"; IF(BalanceAmountValue > 0; BalanceAmountValue; 0);

        "DCREDITS"; IF(BalanceAmountValue < 0; ABS(BalanceAmountValue); 0);

        "DTECRIT"; SELECTEDVALUE(PCA[DTECRIT])

    )

)
Without filter :
 

With filter : 

Do someone has an idea ? 

Thanks you for your helping 🙂 !