Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
A little up because I'm sure we are close to find the solution... But need your help for this 🙂
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
Thanks for your answer!
Yeah I thought on doing this but unfortunately it doesn't work... Everything is then set to 0...
It may be an issue related to the data model. Do you have duplication of sites values there?
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