Pivot and Percent Calculations

[#1]

In a pivot table kind of chart for each row, I have to calculate the value of each cell as a percent of the total for the column.   In the attached spreadsheet,

I have to find the percentage of 23 over 824  (23*100/824)  ( Note: 824 does not include the value in the NULL Country) and show it in the percentage column.

I am facing 2 challenges:

1. Unable to isolate the value for the null contry. (-23.03)

2. When I do the formula Sum(TOTAL_FEES)/Max(aggr(Sum(TOTAL_FEES), PRODUCT)), for the percent column, it is showing the value for only one of the rows.  And I dont understand why it is showing only for one row.

How can I acheve this.  The only thing I need to fill is the percen field. I created the rest of the table.   Please advise.

[sebastiandperei]

Hi pvsrikanth...

1) and 2)

The expression would be:

=Sum({$<COUNTRY={"=Sum(TOTAL_FEES)>0"}>} TOTAL_FEES) / Sum({$<COUNTRY={"=Sum(TOTAL_FEES)>0"}>} TOTAL TOTAL_FEES)*100

You make me doubt about including PRODUCT dimention in the Aggr expression... Please, tell me if i'm wrong, but in the table you only select a PRODUCT, you doesn't show like a dimention.

If i'm rigth, let me explain the use of TOTAL. TOTAL will be affected by your selections, but not for the changes in dimention values. In this case, when you select a particular PRODUCT/s, only will be available the data that have relationship with this PRODUCT. The second "Sum" in expression wont be affected by the dimention, and like the first "Sum", only will compute the >0 Sum of TOTAL_FEES. This last, for exclude Null Country.

Please, let me know if it was useful for you.

[#3]

Thank you sebastiandpereira

The result looks much better.  But the percentage is being calculated as a total of all Products.  It should be calculated only as a percentate of the total of that particular product.

The excel I sent you is exactly what the report is.  The Product is displayed on the top of the Pivot table and the Fees are shown by PRODUCT.

Regards,

Srikanth.

[#4]

One more thing that I noticed is that, when I select the PRODUCT, the percentages are correct.  Only if I dont select the product, the percent is calculated based on the total fees for all products.

[sebastiandperei]

What you want to see when you don't have selected any Product?

[#6]

When I dont select the Product, the other products will be displayed beside it and I expect to see the same numbers for each product, as they would appear when the product is selected. 

I was able to do it following the thread given below.  (Data Island to get the totals). 

Correct Answer by Jonathan Dienst  on Sep 13, 2011 4:32 PM

[sebastiandperei]

If I have understood right, you need that the percentage of the total of products, without take care about the selected one.

The expression would be:

=Sum({$<COUNTRY={"=Sum(TOTAL_FEES)>0"}>} TOTAL_FEES) / Sum({$<COUNTRY={"=Sum(TOTAL_FEES)>0"}, PRODUCT=>} TOTAL TOTAL_FEES)*100

If not, send a reduced copy of your qvw

[sebastiandperei · Accepted solution]

It was useful for you?