Create a line in a visual table to balance amounts.

[KevinSV]

Hello everyone, 
I have a request about a problem i have on my Power BI Desktop.
First of all, i import a table which called PCA with these columns : ANALYT ; CLIENT ; CODEJOUR ; CREDITS ; DEBITS ; DTECRIT ; ETABL ; EXERCICE ; ID_SOCIETE ; LIB2 ; LIGNE ; NUM_COMPT ; NUM_FAC ; PIECE ; REFGESTION ; SECTION ; SOCIETE
I want to add a row on a visual table that i put which balance the amount between Debits and Credits... 
For example, if i have 2000 in Debits and 1500 in credits, I would like to create a line where it will have 500 in credits (so i want to balance in the columns which had the less amount between credits and debits and adding the difference between the total of debits and credits). 

So i created the follow measure : 

BalanceAmount =

VAR TotalCredits =

    CALCULATE(

        SUM(PCA[CREDITS]);

        REMOVEFILTERS(PCA[NUM_COMPT]; PCA[LIB2]; PCA[PIECE]; PCA[LIGNE])

    )

VAR TotalDebits =

    CALCULATE(

        SUM(PCA[DEBITS]);

        REMOVEFILTERS(PCA[NUM_COMPT]; PCA[LIB2]; PCA[PIECE]; PCA[LIGNE])

    )

RETURN

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

And i created the follow table : 

BalanceTable =

VAR BalanceAmountValue = [BalanceAmount]

VAR TCompte = "48700000"

VAR DebitsAmount = IF(BalanceAmountValue > 0; BalanceAmountValue; 0)

VAR CreditsAmount = IF(BalanceAmountValue < 0; ABS(BalanceAmountValue); 0)

RETURN

UNION(

    -- Lignes originales de PCA

    SELECTCOLUMNS(

        PCA;

        "LIBELLE"; PCA[LIB2];

        "CODEJOUR"; PCA[CODEJOUR];

        "SOCIETE"; PCA[SOCIETE];

        "TCOMPTE"; PCA[NUM_COMPT];

        "MDEBITS"; PCA[DEBITS];

        "DCREDITS"; PCA[CREDITS];

        "DTECRIT"; PCA[DTECRIT];

        "PIECE"; PCA[PIECE];

        "LIGNE"; PCA[LIGNE]

    );

    -- Ligne d'équilibre

    ROW(

        "LIBELLE"; "";  

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

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

        "TCOMPTE"; TCompte;  

        "MDEBITS"; DebitsAmount;

        "DCREDITS"; CreditsAmount;

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

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

        "LIGNE"; SELECTEDVALUE(PCA[LIGNE])  

    )

)
Here is the result : 

As you can see it works well when i didn't put some filters, but when i use the slicers the row didn't create as i want : 

I want to adapt the code tha will adapt the row with the filters i put too, for this example, I want a line where i will have an amount of 439.51 in the column MDEBITS (because MDEBIT < DCREDITS).

Thank you for your helping ! 
If you need more details don't hesitate and tell me ! 

[SacheeTh]

Hi @KevinSV,
I have slightly reviced the measures that What I understand in the images,  As I what I belive to ensure your balancing row dynamically adjusts to filters applied via slicers in your Power BI report, you need to modify the logic so that it respects the current filter context. Here's how you can adapt your DAX:

---

Revised Measure for BalanceAmount

This measure ensures the calculation dynamically respects slicer filters.

BalanceAmount =
VAR TotalCredits =
    SUM(PCA[CREDITS])
VAR TotalDebits =
    SUM(PCA[DEBITS])
RETURN
    TotalDebits - TotalCredits

---

Revised Calculated Table for BalanceTable

The balancing row should also respect the current filter context by using ADDCOLUMNS and incorporating slicer filters dynamically.

BalanceTable =
VAR BalanceAmountValue = [BalanceAmount]
VAR TCompte = "48700000"
VAR DebitsAmount = IF(BalanceAmountValue > 0, BalanceAmountValue, 0)
VAR CreditsAmount = IF(BalanceAmountValue < 0, ABS(BalanceAmountValue), 0)
RETURN
UNION(
    -- Original rows from PCA
    SELECTCOLUMNS(
        PCA,
        "LIBELLE", PCA[LIB2],
        "CODEJOUR", PCA[CODEJOUR],
        "SOCIETE", PCA[SOCIETE],
        "TCOMPTE", PCA[NUM_COMPT],
        "MDEBITS", PCA[DEBITS],
        "DCREDITS", PCA[CREDITS],
        "DTECRIT", PCA[DTECRIT],
        "PIECE", PCA[PIECE],
        "LIGNE", PCA[LIGNE]
    ),
    -- Balancing row
    ROW(
        "LIBELLE", "Balancing Line",
        "CODEJOUR", SELECTEDVALUE(PCA[CODEJOUR]),
        "SOCIETE", SELECTEDVALUE(PCA[SOCIETE]),
        "TCOMPTE", TCompte,
        "MDEBITS", DebitsAmount,
        "DCREDITS", CreditsAmount,
        "DTECRIT", SELECTEDVALUE(PCA[DTECRIT]),
        "PIECE", "BALANCE",
        "LIGNE", "999"
    )
)

---

Key Changes:

1. SUM in BalanceAmount:

   • Replaced CALCULATE and REMOVEFILTERS with a simple SUM to ensure the measure respects the slicer filters dynamically.

2. SELECTEDVALUE in Balancing Row:

   • Ensures the balancing row inherits filter context for columns like SOCIETE, CODEJOUR, etc.

3. Dynamic Union:

   • The BalanceTable dynamically calculates the balancing row for the filtered data.

---

Testing Steps:

1. Apply slicers and check if the balancing row dynamically adjusts its MDEBITS and DCREDITS values to balance the filtered totals.
2. Verify whether the new row respects other filters applied to the PCA table.

---

This should solve the issue of balancing rows not adapting to slicer filters. Let me know if further tweaks are needed!

[KevinSV]

Hello @SacheeTh , 
Thanks for your reply ! Unfortunately it didn't work. 
When I filter with the slicers, the balancing line don't create dynamically and so disappear. 
Moreover, your measure didn't respect the balance in which column you need to put the amount (between CREDITS and DEBITS). 
Here is your code result without the filter :

As you can see there is no balance (i need the amount in the DEBITS amount, i think i have to add this code in the measure : IF(TotalCredits > TotalDebits; TotalCredits - TotalDebits; TotalDebits - TotalCredits).

And when i add filters : 

here the balancing line is not created dynamically. 
Thanks again for your reply 🙂 ! 

[KevinSV · Accepted solution]

Hi everyone, 
We did it ! 
So I create a simple measure : 

Différence = ABS(SUM(PCA[DEBITS]) - SUM(PCA[CREDITS]))
and use it in a new table :

TableAvecEquilibrage =

VAR TableBase =

    SUMMARIZE(

        PCA,

        PCA[SOCIETE],

        PCA[CODEJOUR],

        PCA[DTECRIT],

        "TotalDebit", SUM(PCA[DEBITS]),

        "TotalCredit", SUM(PCA[CREDITS]),

        "Difference", [Différence],  

        "Libelle", MAX(PCA[LIB2]),

        "TCOMPTE", MAX(PCA[NUM_COMPT])

    )

RETURN

UNION(

    -- Partie 1 : Table des lignes existantes (sans agrégation)

    SELECTCOLUMNS(

        PCA,

        "SOCIETE", PCA[SOCIETE],

        "CODEJOUR", PCA[CODEJOUR],

        "DTECRIT", PCA[DTECRIT],

        "DEBITS", PCA[DEBITS],

        "CREDITS", PCA[CREDITS],

        "NUM_COMPT", PCA[NUM_COMPT],

        "LIB2", PCA[LIB2]

    ),

    -- Partie 2 : La ligne d'équilibrage

    SELECTCOLUMNS(

        TableBase,

        "SOCIETE", [SOCIETE],

        "CODEJOUR", [CODEJOUR],

        "DTECRIT", [DTECRIT],

        "DEBITS", IF([TotalDebit] < [TotalCredit], [Difference], 0),  

        "CREDITS", IF([TotalCredit] < [TotalDebit], [Difference], 0),  

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

        "LIB2", "Equilibrage"

    )

)

here is the result : 

Thank everyone for your helping 🙂 
have a great day ! 

 

[SacheeTh]

[danextian]

As always, please provide a workable sample data (not an image), your expected result from that and the reasoning behind. You may post a url to sanitized copy of your pbix or an excel file in the cloud.