Pixel Perfect - Calculated Total

[ConnorSteph]

Hi all,

I have a table in Pixel Perfect that contains Sales, Budget, Variance to Budget in $, and Variance to Budget in % as the four columns. The problem arises in the totals.

I can sum the first three columns with no problems using the Summary tool, but Summing (or averaging) a column of percentages does not work properly - I need to be able to calculate the Total Variance to Budget in % as a product of the Total Variance to Budget in $ divided by the Total Budget. Is there a way to do this?

If this is impossible I can create a new table within Sense the contains only the totals, and insert that below, however it would be much better if I could do the above as I have to do this for multiple tables.

Many thanks,

Connor

[Lech_Miszkiewicz]

Since you are able to calculate total for Sales and Budget why dont you use those to calculate % on total?

[ConnorSteph]

Hi Lech,

That's actually what I'm asking how to do/if it is possible. Apologies if the question was a simple one, I am new to Pixel Perfect.

How would I do this?

[Ruggero_Piccoli · Accepted solution]

Hi,

You cannot calculate the total of a percentage as sum of the single percentages of the column. You have to create a new calculated field in the PixelPerfect template:

1. right click on the table row in the levels node and select Add a calculated field
2. in the expression insert the formula sum(field1)/sum(field2) (adapt the formula based on your needs) 
3. drag and drop the new calculated field in the template

In the following example I calculate the percentage of each row compared with the total sum of all rows:

Best Regards,

Ruggero

[ConnorSteph]

Hi Ruggero,

Thanks, that worked perfectly! Appreciate it.

[mstoler]

Hello,

I am doing something similar where I need to compute the % change.

The formula is col1/col2-1.

How can I get this to work in the totals?

Thanks,

Michael

[Ruggero_Piccoli]

Hi @mstoler ,

The question is not clear to me, sorry.  Do you want to add a new calculated column? If you want to add totals refer to my previous solution ad adapt the formula to your case.

Best Regards,

Ruggero

[mstoler]

Hello,

Thanks I figured it out.

I need to use the sum function and then the column name.  In my example my columns are already sums so I have the sum twice.

Thanks

Michael

[Ruggero_Piccoli]

Hi,

You can create a new calculated field by using another one:

If this doesn't solve please start a new conversation with all the details of your case, like screenshots of the template, to help us to understand the specific scenario.

Best Regards,

Ruggero

[mstoler]

Hello,

Yes the two items that I needed to take care of are:

1. I need to use the "Sum" in the functions and not just type in "Sum".  Also because my columns are sums I see a nested Sum with the outer "Sum" is the function in Pixel Perfect.

2. This is in my Report Totals but because I am using the function "Sum" I don't need to to set the Summarize. 

It seems to work well now.

Thank You,