topic Re: Suming Ledger Turnover in a Project matrix table in DAX Commands and Tips

Suming Ledger Turnover in a Project matrix table

Geiern — Thu, 19 Dec 2024 09:24:44 GMT

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 😊

Re: Suming Ledger Turnover in a Project matrix table

Kedar_Pande — Thu, 19 Dec 2024 10:00:24 GMT

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

Re: Suming Ledger Turnover in a Project matrix table

Anonymous — Fri, 20 Dec 2024 07:43:38 GMT

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.

Re: Suming Ledger Turnover in a Project matrix table

Geiern — Fri, 20 Dec 2024 10:28:23 GMT

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

Re: Suming Ledger Turnover in a Project matrix table

Geiern — Fri, 20 Dec 2024 10:29:06 GMT

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

Re: Suming Ledger Turnover in a Project matrix table

Anonymous — Mon, 23 Dec 2024 07:07:46 GMT

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