Subscribe Bookmark RSS Feed



Jun 25, 2014

"Percent of Total" Formula Column Utility

This Add-in generates formula columns calculating the percent of total (row-wise) for the selected numeric columns. These columns are especially useful when creating stacked bar or column chart of the columns of interest and you wish for your scale to be percent of total rather than a sum.  


Usage: After installing this Add-in, select the set of columns in the data table, then choose "Percent of Total Formula Columns" from the Add-ins menu. 


Note: The formulas generated by this Add-in will tolerate missing values row-wise for all but one of the selected columns.






 When creating a stacked bar or column chart, the resulting plot is of the composition, rather than the total:


Total SalesTotal Sales



Composition of SalesComposition of Sales






Very helpful!


Hi Julian, Many thanks for this add-in. It's enormously helpful for analysis of compositional data. Much appreciated!


However, to analyze compositional data in a multivariate manner we need to convert row-wise "counts" to Aitchison's 'centered log ratio' (CLR) for all the columns that are components of the composition (e.g. all the columns that are included in the "percent of total" calculation).


It's very laborious to do this manually in JMP (although we do it for small compositions), and for work with gene transcript or microbiome sequencing data it is not even feasible.


The equation is simple, its just the natural log of the ratio of a given component over the *geometric* mean of all the components of the composition. Thus, for a 3 part composition (A, B, C) A is transformed as: LN(A/((A*B*C)^0.33)); B as LN(B/((A*B*C)^0.33))  and so on for C.


Unfortunately I am not fluent in the scripting (yet)... Would you be able to create a version of the 'Percent of Total' Add In that could instead produce columns of CLR transformed values for the composition?  


Particularly, with subcompositional coherence, we are constantly having to make several new different subcompositions on an ongoing basis. If we have a 100 component microbe composition, we might produce 10 or so different subcompositions each having a different set of columns included in the subcomposition... It would be extraordinarily helpful to multi-select columns and produce the CLR transformations.


Thanks much!


Hi @marxx,


I'm not familiar with the centered log ratio but from your description the calculation seems straightforward enough. I put together a version of this add-in that calculates the CLR and posted it here: 

Download CLR Formula Columns Add-in


Would you check to see if this add-in produces the columns you expect?  If this is functioning properly I'll post this as a separate add-in in the user community for others to use. 


I hope this helps!




Hi Julian,

I tested the JMP CLR Add In versus a 6 component composition that I had
created manually... And the add-in you produced matched all the columns
that had been manually produced. I really appreciate it! 


Thanks so much!


[Edit: Looks like screenshot image can't be included, but it works].
[image: Inline image 1]

Article Labels
Article Tags