Hi everyone,
First time posting here. Apologies if I omit something necessary! I am pulling my hair out over this one.
I have a list report with multiple groupings. The column [Total Cost1] is grouped and has a Group Span set to another column [Current Budget Income1].
The summary total auto-created by Report Studio is correct at all levels of subtotals. I need to use these summary totals in other calculations, and I understand you can't reference them if auto-created by Report Studio.
So, I've tried to build the same subtotals myself into singletons, but I just can't get the totals correct. Here's what I'm trying, where [Project Code Group] is a higher level group that I'm aiming to subtotal for.
total(distinct[Total Cost1] for [Project Code Group])
Using distinct removes duplicate numbers that should legitimately exist (same cost on two different rows), but without it, the group spanning is ignored and the total doubles or triples up.
How do I get it to consider the same group span as per above?
Help!
This query was resolved. I used View Tabular Data to see what I was missing. The total was not taking into consideration another field that I needed to narrow down on, so it was duplicating. A good reminder to use the View Tabular Data function more often when things aren't adding up right!
i.e.
total(distinct[Total Cost1] for [Project Code Group],[Other Data Item])