Totals and Subtotals in calculated columns

John Jacobsen

March 05, 2021 08:14

Is there a workaround so that the totals and subtotals in calculated columns shows the sums of the column instead of the calculation of the individual row?

As You can see in the example, the subtotals of the columns “Ny”, “Mængde” and “Pris” does not show the sum of the values. For column “Stop” the same applies, but there is no value, so the subtotal shows zero.

The same issue applies to the Total that should show the sum of all subtotals.

Comments

2 comments

Marlene Knaup
March 05, 2021 10:09

Hi John, yes there is a workaround :)

You need to add a calculated measure instead of a calculated column. Having done this, you can add a Total as a calculated row.

Then you can hide the automatic grand totals, because you have your own total.

If you need a step by step instruction please tell me.

1
Ole Dyring
March 06, 2021 18:02 (Edited March 06, 2021 18:02)
Hi John,

I have an approach that I use very often in situations similar to yours. Generally, it can be summed up like this: "If you are at the lowest level of a hierarchy then do this, else do that."

The lowest level can be defined as the level that does not have any children. The other levels have at least one child.

This syntax can be used to determine if you are at the lowest level:
```
if allcount(d1, all(c), m1) = 0 then 1 else 0
```
In your case, it might be used like this:
```
if allcount(d1, all(c), m1) = 0 then sum(d1, 0, m1) else sum(d1, all(c), m1)
```
Of course, I cannot tell if this will work 100% in your case - I don't know your exact calculation requirements. Also, you may need to replace the "d1" and "m1" references with something that matches your case.

BR / Ole
3

Please sign in to leave a comment.

Didn't find what you were looking for?

New post