Set analysis in a pivot table

[Anonymous]

I have the following pivot table: see attached.

Rows = Month, measures

Columns = Marketing type

Measures = multiple, including "% of Loans"

% of Loans is calculated as:  that month's "Loans Generated" divided by that columns sum of "Loans Generated" across all the months.

I'm trying different versions of set analysis and total, but haven't been able to get the working formula yet.  Can someone help? Thanks!

[Anonymous]

The following formula works in coming up with the column totals:  Sum( Aggr( sum(S7_Ct),S7_ADType) )

This will be the correct denominator in the "% of Loans" formula, so that looks like:

Sum(S7_Ct)

/

Sum(Aggr(sum(S7_Ct),S7_ADType))

However ... for testing, when I put the formula in the "% of Loans" measure in the pivot table, it ONLY puts the correct column total in the month of April 2017.  For whatever reason, all the other months show up as $0.  See attached for a screenshot of the formula in action.

[Digvijay_Singh]

Can you check the result of this -

Sum(S7_Ct)

/

Sum(Total S7_Ct)

If possible please share sample data to look further.

[Anonymous]

Sum(Total S7_Ct) provides the total sum for all columns, not just the applicable column.

[Anonymous]

When I filter the pivot table on months, it seems to fill in the value for ONLY the month that comes first alphabetically.

For example, when I take out the month of April (which is the only month that had the number filled in) and only select Dec - Jan - Feb and July (randomly), only the month of December has the number completed.  The rest is $0.

Why would only the 'first' (alphabetically) month show up with this calculation?

[Digvijay_Singh · Accepted solution]

I think what you are trying should show the correct for other months, can you share sample data/app, this below one is the other alternative which I didn't put properly in the last post -

Sum(S7_Ct)

/

Sum(Total<S7_ADType> S7_Ct)

Also do you see any difference if you make your measures as columns and type as rows?

[Digvijay_Singh]

I think you need total by both Month and Type so, it should be like this -

Sum(Aggr(sum(S7_Ct),Monthfield,S7_ADType))

It was showing other months null as it calculates once for all months, I think if you would have added nodistinct in Aggr(), it would have shown same value in all the months.

[Anonymous]

For ease of troubleshooting, I'm only placing the denominator formula (as in, this should show the total number of old loans per column).

See screenshot "pivot_table": when I use Sum(Aggr(sum(S7_Ct),Monthfield,S7_ADType)), it basically shows the count per ADType and per Month. 

However, it should do the sum without looking at Month.

See screenshot "pivot_table2": without the extra aggregation on Monthfield, it does calculate correctly ... but only for the first alphabetical month!?

[Anonymous]

See attached for a sample App.  As you see, the column total only gets shown once.  I need it to show for each row, as I would expect the formula to do.

[Anonymous]

Digvijay,

I was sure I tested this formula suggestion but it does work!  Thank you!

Sum(TOTAL <Product> UnitSales)