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 🙂 

[Chanty4u]

For problem 1 try with aggr 

Sum(

  Aggr(

    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),

    [MASTER_CAL.Year], [V_BelegKopfVert.Center]

  )

)

For problem 2 exclude employee from set analysis 

Sum(

  Aggr(

    Sum({<

         V_BelegPos.offen={0},

         V_BelegPos.BelegArt={'R','G'},

         V_BelegKopf.AuftragsArt={''},

         MASTER_CAL.Year={">=$(=Year(Today())-2)<=$(=Year(Today()))"},

         Employees=

       >} V_BelegPos.pA_Gesamtpreis_HW),

    [MASTER_CAL.Year], [V_BelegKopfVert.Cent

er]

  )

)

[marcus_sommer]

You need to extend the set statements to those information which selections should be ignored, for example the following one which ignored selections on the center:

Sum(Total <[MASTER_CAL.Year], [V_BelegKopfVert.Center]>
  {
    < [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
)

[reporting_neu]

Thank you for your answer. Unfortunately, the formula isn't working properly. Now, it only displays random totals occasionally, and otherwise, just 0. Very strange.

[reporting_neu]

Thank you very much for your answer.

I'm not entirely sure I understood you. I assumed that in this part:

Sum(Total <[MASTER_CAL.Year], [V_BelegKopfVert.Center]>

 I thought that I would enter the fields for which totals need to be calculated and whose dimensions should be taken into account.

Example:

Year	Sales of all centers per year	Center	Sales per center per year	Employee	Sales per employee per year
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

[marcus_sommer]

A TOTAL statement causes an ignoring of all object-dimensions unless those ones which are extra specified within the < > which are further considered. It has no impact in regard to the selection state - set per selections and/or extended/modified in any way by a set analysis statement.

[reporting_neu]

Is there another way to calculate the revenue per center per year and per year so that the table remains selectable?

[marcus_sommer]

The above shown logic should be working - and might be applied and/or combined with more advanced approaches - but I would suggest to develop it in a simpler way and step by step. This means removing all extra conditions / dimensions and starting with 2 dimensions and n parallel expressions, like:

sum(Field)
sum(TOTAL Field)
sum(TOTAL <Dim1> Field)
sum({< Dim1 >} Field)

against a per selections reduced subset - just to trace the results by changes on the Total / set statements on one glance. In a quite short time are n variations and more extended attempts possible and it could be then adapted to the real requirements.

[reporting_neu]

It just doesn't work.

I've tried the options you mentioned, but I just can't get the result I need.

To recap:
I need the employee revenue per year and center. Okay, no problem, because the table contains the dimensions Year and Center, as well as Employee.

But I also need the centers' revenue per year, regardless of the employee. And I could only get the total using "TOTAL <Year, Center>". BUT: If I select an employee, the values ​​change and don't remain TOTAL, but refer to the employee. If I include "(1/$/ )" Employee=", I can't select employees.

Really difficult....

[marcus_sommer]

In your origin description it was mentioned that the table should contain 3 dimensions and at least 3 expressions. Two of the expressions should return some kind of TOTAL and not reflecting on certain selections. Exactly this could be reached with the hinted approaches of:

sum(TOTAL Field)
sum(TOTAL <Dim1> Field)
sum({< Dim1 >} Field)

which might be extended and/or combined in various ways, like:

sum(TOTAL Field)
sum(TOTAL <Dim1, Dim2, Dim3> Field)
sum({< Dim1, Dim2, Dim3 >} Field)

sum({< Dim1, Dim2> } TOTAL <Dim1> Field)

If the requirements are different then some adjustments are needed.