Table with different sum if expressions within row and column level

[TestingForTestPurposes]

Hi all,

this might be a bit of a complex problem as I am forced to format a table like an Excel table.
What I want to achieve is a table that displays the budget that was already invested in 4 different products and the over all budget that is still available for the those products. The column for the assigned budget is relativity easy to generate for me. However I struggle with creating the column for the volume that is still available.

	Assigned Volume	Available Volume
Product A	SUM(IF(product='A', budget,0))	SUM(IF(eu=1 and approval<>0, budget,0)) - SUM(IF(product='A', budget,0))
Product B	SUM(IF(product='B', budget,0))	SUM(IF(eu=1 and approval<>0, budget,0)) - SUM(IF(product='B', budget,0))
Product C	SUM(IF(product='C', budget,0))	SUM(IF(eu=1 and approval<>0, budget,0)) - SUM(IF(product='C', budget,0))
Product D	SUM(IF(product='D', budget,0))	SUM(IF(eu=1 and approval<>0 and level2_approval<>0, budget,0))

Yes it is correct that for Product D the available volume does not need to be subtracted by the already assigned volume.

For the assigned volume column I used the following function: =sum({<product={'A', 'B', 'C', 'D'}>}budget). But I haven't found a solution for the Available Volume column yet.
For me it is irrelevant if I solve this via a solution within the load script by creating a new table, creating variables for the given table fields or if I use a solution within the frond end (i.e. with set expressions).

I already created a table out of different KPI tiles (see below) however this is not the solution I am aiming for

Looking forward to your suggestions!

[pravinboniface]

How about something like this:

// Product A Available Volume
SUM({<eu={1},approval={'*'}-{1}>}budget) - SUM({<product={'A'}>}budget)
// Product B Available Volume
SUM({<eu={1},approval={'*'}-{1}>}budget) - SUM({<product={'B'}>}budget)
// Product C Available Volume
SUM({<eu={1},approval={'*'}-{1}>}budget) - SUM({<product={'C'}>}budget)
// Product D Available Volume
SUM({<eu={1},approval={'*'}-{1},level2_approval={'*'}-{0}>}budget)

[TestingForTestPurposes]

But how do I get this into one column of the table? Simply inserting those functions into one function line of the table visualization won't work right?

[pravinboniface]

Does something like this work for you?  I have not tried it out with sample data.

// Use TOTAL to get the overall budget
// Sum(budget) will be for a given product
// Use if() to apply a specific formula for product D
If (product<> 'D',
SUM({<eu={1},approval={'*'}-{1}>}TOTAL budget) - SUM(budget),

SUM({<eu={1},approval={'*'}-{1},level2_approval={'*'}-{0}>}TOTAL budget)
)

[TestingForTestPurposes]

Unfortunately I couldn't get it to work because I always get the error message that there is still an error in the set modifier expression. But I really like the approach!

It seams like that there is an issue with the "else" part of the if expression. However even when I run only the first part only empty values are listed in the table.