Limiting the dimension values in pivot table.

[Anonymous]

Hi All,

I'm trying to create a pivot table chart in which the dimension is Region and their values are APAC,EMEA,NA and LATAM and I'm summing the sales value for these dimensions over another dimension which is year. Now only APAC is divided into APAC, APAC Direct and APAC Channel.

Now:

1. I'm trying to come up with single APAC value which should have the sum of sales for APAC + APAC Direct + APAC Channel

2. Is it possible to show only APAC value in pivot table but the final sum should show the sum for APAC Direct and APAC Channel also.

Please suggest!!!

-Abhishek

[vgutkovsky]

Simplest way to do this would be to just create a higher-level field in the data model. Something like this:

if(substringcount(Region,'APAC')>0,'APAC',Region) as [Region High Level]

and then just use this new field in your pivot table instead of the current Region field.

Regards,

Vlad

[Anonymous]

Dear Vlad,

Thanks for replying and for my query 1, it is working absolutely fine. But I'm also trying to figure out the answer for Query 2 where I just want to hide the APAC Direct and APAC channel vlaue in pivot and only show the APAC value but the final total for the pivot, APAC Direct and APAC channel will be included.

Appreciate if you can provide me a workaround for that.

Regards,

-Abhishek

[vgutkovsky]

Sounds like you just need to add your original Region field as a dimension after the new Region High Level field. Then use the + sign in the pivot table to show totals at that level. Also, under Presentation, enabled Partial Sums for the original Region level.

Regards,

Vlad