8 Replies Latest reply on Jul 24, 2019 1:06 PM by 1060597

# Pivot table Sub Total for records within each Accounting Period.

I want to achieve Total column only for records with price mentioned above for each AP excluding columns with "WAC".

Ex: For AP 01, I Want to have Total column Before "WAC", which totals values with prices 5.111 and 5.112 excluding values in "WAC".

Thanks

Prabhu • ###### 1. Re: Pivot table Sub Total for records within each Accounting Period.

You can't use the automated totals/subtotals options of the pivot, because they would be on everything or nothing.

Depending on what your "5.111", "5.112" and "WAC" are (measures, values of a dimension attribute etc.) you will need to add an element there with your calculation.

If it's measures add a new one with the required formula, if it's values of a dimension attribute you could try adding a new group entry or a calculated item.

• ###### 2. Re: Pivot table Sub Total for records within each Accounting Period.

Adding to Gianni's response, you will need to create a column with a formula with logic similar to this:

FILTER(SUM(measure BY AP dimension column) USING (2nd dimension column <> 'WAC'))

• ###### 3. Re: Pivot table Sub Total for records within each Accounting Period.

Hi

Thanks, I will try this, Yes values "5.111", "5.112" and "WAC" are measures. Yes automated subtotals are for everything or nothing.

Thanks

Prabhu

• ###### 4. Re: Pivot table Sub Total for records within each Accounting Period.

Thanks Joel, I will try this option and let you know what happened.

• ###### 5. Re: Pivot table Sub Total for records within each Accounting Period.

I created a column with formula which is another measure, But how does this work as pivot subtotal?

• ###### 7. Re: Pivot table Sub Total for records within each Accounting Period.

How can the new measure you created be a row?

How can that be a measure if few post ago you said "5.111", "5.112", "WAC" are the measures? That's impossible!

The measure it's either your rows or your columns, it can't be both at the same time, you better figure out exactly what is what.

• ###### 8. Re: Pivot table Sub Total for records within each Accounting Period.

Sorry my mistake, Price, quantity and value are all part of same Aggregate Fact table with item and Calendar as dimension tables. But here we are using quantity and value as measures while price is used as a pivot column,that's the reason i am having this issue.

Thanks

Prabhu