Get Free Trial

Using Sub Totals in a calculated Measure

Answered
Ravi asked on September 13, 2021

Hi,
Is it possible to use the sub total of a column in the formula for a calculated measure? I see a way to get a grand total in a calculated measure thru the formula(https://www.flexmonster.com/question/using-grand-totals-in-a-calculated-measure/?hilite=%22use%22%2C%22grand%22%2C%22total%22%2C%22formula%22)
 Is there any such formula to use subtotal? 

5 answers

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster September 13, 2021

Hello,
 
Thank you for posting on our forum.
 
Please note that the mentioned forum thread describes particular aggregations (% of column, % of row) available in Flexmonster. Grand totals are used for a better explanation of how these aggregations operate.
 
In fact, it is not possible to refer to grand totals or subtotals directly in the formula.
 
Please let us know if any other questions arise.
 
Kind regards,
Illia

Public
Ravi September 15, 2021

Thanks, Illia Yatsyshyn!
 
Can we expect “referencing sub-total and grand-total” in Flexmonster in the future versions? 

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster September 16, 2021

Hello,
 
We have sent the response to your question via email.
 
Feel free to reach out if additional questions arise.
 
Kind regards,
Illia

Public
Kevin Nguyen September 16, 2021

Hello,
I also have users who would like to use the Grand Totals and Subtotals in their calculated measure’s formula.
The current workaround we are using is to use the sum and percent of column aggregations to calculated the grand total in their calculated measures.
Example:
(sum(“Measure”) / percentofcolumn(“Measure”)) * 100 = Grand Total of Measure
 
This is obviously not ideal, but it can work in a pinch.

 

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster September 20, 2021

Hello, Kevin,
 
Thank you for sharing this workaround on our forum.
 
Kind regards,
Illia

Please login or Register to Submit Answer