topic Re: Subtotal Incorrect in Pivot Table with Aggr Condition in App Development

Subtotal Incorrect in Pivot Table with Aggr Condition

kcollorig — Sat, 16 Nov 2024 00:38:23 GMT

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!

Re: Subtotal Incorrect in Pivot Table with Aggr Condition

kcollorig — Thu, 11 Feb 2021 14:58:45 GMT

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

Re: Subtotal Incorrect in Pivot Table with Aggr Condition

marcus_sommer — Thu, 11 Feb 2021 16:38:19 GMT

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

Re: Subtotal Incorrect in Pivot Table with Aggr Condition

kcollorig — Thu, 11 Feb 2021 17:04:21 GMT

Thanks for your answer!

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

Re: Subtotal Incorrect in Pivot Table with Aggr Condition

sonkumamon — Thu, 11 Feb 2021 21:07:26 GMT

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

Re: Subtotal Incorrect in Pivot Table with Aggr Condition

marcus_sommer — Fri, 12 Feb 2021 08:52:23 GMT

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