I’ve seen similar issues to this but haven’t seen a resolution that works as of yet. Here’s the scenario (using Webi 4.1):
I have 2 vertical table blocks each containing a measure. Each table also has an input control for one dimension. There is only one query and no sections or breaks or any other filtering and one report tab.
I need to add the sum of the measure from table 1 (Measure1) to the sum of the measure from table 2 (Measure2) and add this to a blank cell or to a third table block. I’m simply just trying to add the 2 measure amounts from the 2 tables to show a grand total (But I can’t combine the 2 tables as one for various reasons, no separate queries).
I have tried the following formulas with no success:
=(Sum ([Measure1]) Where (BlockName([Measure1])=“Block1”))+(Sum ([Measure2]) Where (BlockName([Measure2])=“Block2”))
The above formula produces an error message because of the measure being inside of the BlockName parentheses.
=(Sum ([Measure1]) Where (BlockName()=“Block1”))+(Sum ([Measure2]) Where (BlockName()=“Block2”))
=(Sum (If(IsNull([Measure1];0;[Measure1]))) Where (BlockName()=“Block1”))+(Sum (If(IsNull([Measure2];0;[Measure2]))Where (BlockName()=“Block2”))
The above formulas returned null values
=Sum([Measure1]) IN (BlockName([Measure1])=“Block 1”) + Sum([Measure2]) IN (BlockName([Measure2])=“Block 2”)
This produced an error message for invalid identifier.
I also adjusted and tried all of these formulas to just return the measure from just table 1 but had the same results.
I’m not sure why I got null values on the second formula as it seems logically correct.
Just to clarify, are [Measure 1] and [Measure 2] variables you’ve created, or are you just using that to show where you have summed the table using the sum button?
The measures are from the universe, and were not created in the Webi report. However, I did try creating Webi variables for this as well after your reply but got the same results. I created the Webi variables for the Sum (Sum([variable1]) and also tried reproducing the Universe Measure as a Webi variable, but no difference.
The measures are both Type = Measure Data Type = numeric and one is a Summation and the other is a Count with both having a Sum Projection Function.
Yes both are numeric and show right justified in Webi
3 & 4 - I have tried these as both a Total of the block and as row. First time I tried Sum(Measure1), Sum(Measure2) and then I tried simply as Measure1, and Measure2.
And if you’re anything like me, I OFTEN make things a lot harder than I need too…lol. Just making sure you tried =Sum([Measure 1]+[Measure 2]). If they are already totals of the variables.
Sorry, I didn’t see that you were asking about screenshots and I have attached.
Note that the screenshots show the blocks after Input Control selection. The Variables block (on bottom) needs to calculate the sum of hours from both blocks above it after input selection. As you can see from the screen shot it is not calculating correctly. It’s actually calculating the sum of hours after applying the filters from both blocks together as opposed to individually. In other words it is filtering the 2 systems selected and then further filtering for the 2 Statuses selected then summing. It should be filtering on the 2 systems selected (for all statuses, not just those selected) and then summing hours for block 1, and then filtering the 2 statuses selected (for all systems, not just those selected) and then summing the hours from block 2 and finally adding these 2 summations together for a grand total. You can see from the 1st screen shot that there are more than 2 statuses and systems returned from the query,
I’ve tried all kinds of calculations methods including NoFilters() and various calculation contexts (ForEach, ForAll, et.c). I just can’t get it to sum correctly.
