Using Sub Totals in a calculated Measure

posted on September 13th, 2021

Closed

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 ⋅ 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 ⋅ 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 ⋅ Flexmonster ⋅ September 20, 2021

Hello, Kevin,

Thank you for sharing this workaround on our forum.

Kind regards,
Illia

This question is now closed