Subtotal Incorrect in Pivot Table with Aggr Condition

[kcollorig]

Hello,

I'm having a problem with the subtotal value of my Pivot Table. I'm trying to dynamically assign a value depending on a condition for Dimension 2 and to have the sum of these values for Dimension 1.

Here are the parameters of the table :

Dimension 1 : COMPANY

Dimension 2 : SITE

Column : DATE

Measure : 

If(Count(Distinct[ACTIVITIES]) < Sum(Aggr(sum(STAFF), [DATE],[COMPANY],[SITE])),

  Sum(Aggr(100, [DATE],[COMPANY],[SITE])),

  0
)

So it is working fine for the SITE level (NANTES, SAINT ELOI...) but not for the COMPANY level... The sum does not correspond to the SITE level (100 + 100 = 200).

Any idea ?

Thanks in advance for your help!

[kcollorig]

A little up because I'm sure we are close to find the solution... But need your help for this 🙂

[marcus_sommer]

Maybe something like this:

sum(aggr(

If(Count(Distinct[ACTIVITIES]) < Sum(Aggr(sum(STAFF), [DATE],[COMPANY],[SITE])),

  Sum(Aggr(100, [DATE],[COMPANY],[SITE])),

  0
),

[DATE],[COMPANY],[SITE]))

- Marcus

[kcollorig]

Thanks for your answer!

Yeah I thought on doing this but unfortunately it doesn't work... Everything is then set to 0...

[sonkumamon]

It may be an issue related to the data model. Do you have duplication of sites values there?

[marcus_sommer]

Quite often worked this kind of aggregating the results of the inner calculation on their row-level because the set conditions couldn't be applied/fulfilled on the higher TOTAL levels respectively return there other results. But your expression looks a bit uncommon and therefore I suggest to review your object-calculation in regard with the datamodel.

Nevertheless it could be that you need different expressions on the different dimension-levels of your pivot. This could be reached with something like:

if(dimensionality() < 1, expr1, expr2)

To see which row has which level put dimensionality() directly as an expression in your object.

- Marcus