Need 'Partial Sums', when Dim has both Count & Sum

[Saravanan_Desingh]

I have a situation.

My Calculated Dimension is as follows (Using a Field & Text):

=Pick(Dim,[Strategic  Location],'Open Req')

My Expression is (having both Count & Sum):

If(Dim=1,Count($(vAggrFld)),Sum(OpenReq))

This is Pivot Table. If I select 'Show Partial Sums' in the Presentation tab, I'm getting only the Sum values not the Sum of Rows.

I'm looking something similar to Straight Table - Sum of rows

Below Expression is also not working..

RangeSum(If(Dim=1,Count($(vAggrFld)),Sum(OpenReq)))

Can you please help me? Thank you.

[sunny_talwar]

Not sure if I understand the requirement completely. Do you have a sample you can share?

[Saravanan_Desingh]

Hi Sunny,

Please see that attachment.I'm expecting the 'Total HC' as 1341, which is sum of all rows.

But I'm getting only 72, which is sum of the OpenReq Rows only.

[swuehl · Accepted solution]

Try

=If(Dimensionality=0,

Sum( Aggr( If(Dim=1,Count($(vAggrFld)),Sum(OpenReq)), Dim)),

If(Dim=1,Count($(vAggrFld)),Sum(OpenReq))

)

[in general, sum-of-rows in pivot tables can be achieved with advanced aggregation. Here, your setting with calculated dimensions is a bit more complex, so if above doesn't work, please post a small sample QVW.]

[Saravanan_Desingh]

Hi Stefan,

Thanks for your response. But the expression didn't work.

Being inspired by your code, I did the below expression and it worked.

Thanks again.

If(Dimensionality()<>0,
If(Dim=1,Count($(vAggrFld)),Sum(OpenReq))
,RangeSum(Count($(vAggrFld)),Sum(OpenReq))
)

[Saravanan_Desingh]

Hi Stefan,

I sincerely sorry.

I just missed the () in the Dimensionality.

Your code is working. Sorry again.

=If(Dimensionality()=0,

Sum( Aggr( If(Dim=1,Count($(vAggrFld)),Sum(OpenReq)), Dim)),

If(Dim=1,Count($(vAggrFld)),Sum(OpenReq))

)

[swuehl]

No, that was me

Glad that it worked. Your solution looks a bit more simple, though.

[Saravanan_Desingh]

Thanks, Stefan ..