Cannot filter at Total

[reporting_neu]

Hello, I would like to display the following dimensions in a table:

• Year
• Center
• Employees

And the following key figures for the last three years:

• Sales of all centers per year
• Sales per center per year
• Sales per employee per year

I calculated the key figure for "Sales per employee per year" using the following formula:

Sales per employee per year

SUM({
    < V_BelegPos.offen = {0},
      V_BelegPos.BelegArt = {"R","G"},
      V_BelegKopf.AuftragsArt = {""},
      MASTER_CAL.Year = {">=$(=Year(Today())-2)<=$(=Year(Today()))"}
    >
} V_BelegPos.pA_Gesamtpreis_HW
)

The formula works and I can filter the dimensions as I wish.

Now I would like to calculate the turnover per center and year and have used the following formula:

Sales per center per year

Sum(Total <[MASTER_CAL.Year], [V_BelegKopfVert.Center]>
  {
    < V_BelegPos.offen = {0},
      V_BelegPos.BelegArt = {"R","G"},
      V_BelegKopf.AuftragsArt = {""},
      MASTER_CAL.Year = {">=$(=Year(Today())-2)<=$(=Year(Today()))"}
      >
  }
  V_BelegPos.pA_Gesamtpreis_HW
)

The calculation works so far, but I can't make any further selections from this point on. For example, if I select a year, all the years remain the same. Only the values ​​change to 0.

If I filter for an employee, only that employee is selected. BUT: There's a negative side effect – my second problem. The total value of the center changes due to the selection, which shouldn't be happening.

Can anyone help me with both of these problems?

• Enable filtering on all dimensions
• The values ​​don't change when a dimension is filtered

I hope you can help me 🙂 

[reporting_neu]

Okay, thanks for your feedback.

I'll show you the formulas.

Total for year

SUM(TOTAL <Year> SALES)

Total for center

SUM(TOTAL <Year, Center> SALES)

Total for employees

SUM(SALES)

As long as nothing is selected, everything is fine.

Year	Sum Year	Center	Sum Center	Employees	Sum Employees
2025	20.000	North	15.000	John	7.000
2024	15.000	North	10.000	John	8.000
2023	12.000	North	8.000	John	6.000
2025	20.000	East	5.000	Peter	4.000
2024	15.000	East	5.000	Peter	3.000
2023	12.000	East	4.000	Peter	2.000

If I select the employee "John":

Year	Sum Year	Center	Sum Center	Employees	Sum Employees
2025	7.000	North	7.000	John	7.000
2024	8.000	North	8.000	John	8.000
2023	6.000	North	6.000	John	6.000

If I exclude the employees, it is no longer possible to select them accurately.

So I continue to adjust the formula.

Total for year

SUM(TOTAL <Year> {<Center=, Employees= >}SALES)​

Total for center

SUM(TOTAL <Year, Center> {<Employees= >}SALES)​

If I select the employee "John":

Year	Sum Year	Center	Sum Center	Employees	Sum Employees
2025	20.000	North	15.000	John	7.000
2024	15.000	North	10.000	John	8.000
2023	12.000	North	8.000	John	6.000
2025	20.000	East	5.000	Peter	0
2024	15.000	East	5.000	Peter	0
2023	12.000	East	4.000	Peter	0

If I select the Center "North" and employee "John" :

Year	Sum Year	Center	Sum Center	Employees	Sum Employees
2025	20.000	North	15.000	John	7.000
2024	15.000	North	10.000	John	8.000
2023	12.000	North	8.000	John	6.000
2025	20.000	East	0	Peter	0
2024	15.000	East	0	Peter	0
2023	12.000	East	0	Peter	0

Do you see the problem?

[marcus_sommer]

The expectation isn't clear for me - which values should be shown instead of the red ZERO or should these rows entirely removed?

[reporting_neu]

Because in this example the value "John" was selected in the "Employee" dimension, all other values ​​should be hidden. However, due to "{<Employees = >}" and  "{<Center =, Employees = >}, the dimensions remain and values ​​are set to zero.

However, if I remove "{<Employee = >}" and "{<Middle =, Employee = >}", the value becomes indented for each dimension when selecting an employee, as in the second table in the last post.

[marcus_sommer]

Ok. I see the total and the set statement ignoring certain selections are conflicting with each other. Solving such scenarios within a single calculation could become quite challenging. Therefore I would try a simpler way by combining two calculations in which one calculates the wanted results and the second one acts as a boolean condition if this result should be displayed or not. This may look like:

sum({< Dim3> } total <Dim1> Field) * sign(sum(Field))