States on Calculated Totals

#1

Hey all!
I was hoping to see if anyone has encountered this as well and if anyone knows of any solutions for it.

Here’s my issue!

I uploaded an excel file with a list of buildings within markets of a company.
The file had max employees and the number of employees participating in a specific program.

While calculating the percentage of employee in a specific program - I notices the grand and sub totals were the sum of all percentages within their categories. I found a fix for this with this dundas documentation: https://www.dundas.com/Support/learning/documentation/design-view/using-a-custom-total-calculation

After having recalc’d the totals - I found a new issue. When attempting the add a state and color/highlight percent ranges - dundas is reading the grand totals much lower than is actually posted in the cell (see images below).

Does anyone know how I can get the states to display properly on totals and subtotals?

image
image.png860×340 16.2 KB

image

image

#2

It looks like you added a constant (formula) measure to configure your states. It is possible that the aggregator for the constant measure doesn’t match the aggregation rule your have for your totals. One way to quickly check that is by clicking on the visualization options under the table data analysis panel and then adding that constant measure to the columns. You will then see the actual value calculated for the constant value in order to calculate the state(by default it’s using a sum aggregation). You may need to change the constant measure aggregator.
image

Let us know if that helps to understand the state calculation.

#3

You’re the best! That works!. I didnt understand that the constants were also being aggregated. Thank you for your help!

3 Likes
#4

And a year and a half later I’m in the same situation and found the answer in this thread in the suggestions that came up when trying to create a new post.

Thanks Ariel - your work at Dundas lives on!

And thanks to everyone who setup this wonderful resource

#5

I passed along your note to Ariel. I’m sure he’ll be happy to hear it.