Suming Ledger Turnover in a Project matrix table

[Geiern]

Dear Community,

I have a Project table and Ledger table.
Project table has a line pr month, pr ProjectID.
All instances in Ledger table should have a ProjectID
It is possible to have BLANK ProjectID in Ledger, so I make a “Project” out of the blanks and collect them.
It is also possible to have ProjectIDs in the Ledger that does not exist in the Project table so I make a “Project” out of these too.

In the Project table I make a calculated column like this:
Turnover =

SUMX (FILTER (
Ledger,

        Project[StartDate] <= Ledger[ValDate]

        && Project[EndDate] >= Ledger[ValDate]

        && Project[Owning Business Unit] = Ledger[Owning Business Unit]

        && Ledger[AcNo] >= 3000

        && Ledger[AcNo] < 4000

            && SWITCH (

                TRUE,

                Project[PID] = Ledger[PID], Ledger[PID] = Project[PID],     // Success

                ISBLANK (Ledger[PID]), Project[PID] = "ERR-99",               // Missing PID in Ledger
                Project[PID] = "ERR-98"                                                     // Missing PID in Project                                                               

            )

    ),

    Ledger[AcAm(Base)]

)

Output of this calculated column is working fine on both TRUE statements, but it sums all instances in the ELSE statement (including the error).
Thanks for any help 😊  

[Anonymous]

Hi @Geiern ,

The issue you are facing is that the formula sums all instances in the ELSE statement, including the errors. To address this, you can modify the formula to handle the ELSE cases separately and ensure that only valid instances are summed.

You can modify your formula like below:

Turnover = 
SUMX (
    FILTER (
        Ledger,
        Project[StartDate] <= Ledger[ValDate] &&
        Project[EndDate] >= Ledger[ValDate] &&
        Project[Owning Business Unit] = Ledger[Owning Business Unit] &&
        Ledger[AcNo] >= 3000 &&
        Ledger[AcNo] < 4000 &&
        (
            (Project[PID] = Ledger[PID] && NOT ISBLANK(Ledger[PID])) || 
            (ISBLANK(Ledger[PID]) && Project[PID] = "ERR-99") || 
            (Project[PID] = "ERR-98")
        )
    ),
    Ledger[AcAm(Base)]
)

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.

[Geiern]

Hi Adamk Kong,
Thanks for you're involvement.
Your solution still adds up all instances in the ERR-98

[Anonymous]

Hi @Geiern ,

Please provide the test data related to the formula as well as the model, so that I can answer your question as soon as possible.

Adamk Kong

[Kedar_Pande]

@Geiern 

To prevent unwanted rows from being included, explicitly filter them out using the FILTER function before applying SUMX.

Turnover =
SUMX (
FILTER (
Ledger,
Project[StartDate] <= Ledger[ValDate]
&& Project[EndDate] >= Ledger[ValDate]
&& Project[Owning Business Unit] = Ledger[Owning Business Unit]
&& Ledger[AcNo] >= 3000
&& Ledger[AcNo] < 4000
&& SWITCH (
TRUE,
Project[PID] = Ledger[PID], TRUE, 
ISBLANK ( Ledger[PID] ) && Project[PID] = "ERR-99", TRUE, 
Project[PID] = "ERR-98", TRUE, 
FALSE 
)
),
Ledger[AcAm(Base)]
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Geiern]

Hi Kedar_Pande,
Thanks for you're involvement.
Your solution still adds up all instances in the ERR-98