Remove unwanted values in Pivot Tables

[JFDemers]

Hi,

I have a pivot table with 3 dimensions (Fund Class Name, Return Type, Benchmark), 1 column with the dimension 'Calendar Year' and 1 Measure which is the Return.

My problem is that the pivot table is populating the lines of the Fund Class Name and Return Type which I want to be empty/blank and only have values for the Benchmark dimension. (see screenshot)

Any ways to achieve this?

Thx in advance

[Chanty4u]

try this

IF([Benchmark]='YourBenchmark', [Return])

[JFDemers]

Sorry but I am not sure I understand you proposition. Each records may have different benchmarks so I am not sure to understand the 'YourBenchmark' portion of the formula.

Attached is a sample of the underlying data from the database.

Thx for the help!

[Parthiban]

Hi,

If you want only benchmark values means then why are you taking Fund Class name dimension. Just ignore that dimension and add Return_type = 'Benchmark' in dimension and then add benchmark dimension. 

Finally u have only the benchmark values.

[Parthiban]

Suppose if you want that Fund class dimension also. Try this expression in your measure

sum( {< Return_type = {'Benchmark'}>}[Return])

[JFDemers]

Because the user wants to see both the portfolio returns and the benchmark returns on top of each other (as seen in the original post) and not only the Benchmark returns. What I am trying to avoid is the return values to be repeated unnescessarly.

Thx

[steeefan]

Are you talking about the Total rows? Have you tried turning them off?

[JFDemers]

Unfortunately no. The Show totals is only for the first dimension of the pivot table and is a total of the entire data set. My issue is the fact that at each dimension levels are repeating the values. See attached.

[JFDemers]

So...it is not exactly what I wanted but if I uncheck the "Indent Rows" option under appearance it gets rid of the duplicate values as it changes the layout of the pivot table. It will do for now.

Thx all for the help! Much appreciated!