
1. Re: Show Filtered Items as Sum Total in Table
Michel Caissie Mar 13, 2017 2:09 PM (in response to David Roscoe)Dave,
you can use lod to compute the Field2 cost.
{FIXED [Field2]: SUM([Cost])}
Next you can modify your calculation1 for
IF [Field2 (cost)] > 600 THEN [Field2] else "Other" END
Using this, you get rid of the Attr() function , and the Calculation1 becomes a dimension. So you can put Calculation1 (copy) on the Rows without the need of Field2 on the Detail.You get a single mark for 'Other' instead of 4, and this mark will get the SUM of only the 'Other' field2 values.
Michel

Test (ed mc).twbx 35.5 KB


2. Re: Show Filtered Items as Sum Total in Table
David Roscoe Mar 14, 2017 8:27 AM (in response to Michel Caissie)Many thanks for your help Michel, I wouldn't have thought to do that.
What you put together worked perfectly, however, my actual data is more complex and I'm having difficulty applying the method.
I have put together another test workbook which mirrors my actual data more closely.
As you can see I have 2 calculated fields which are working out variances across different periods (Variance 1 & 2).
I then have a field named Variance 3 which totals the first two.
As there are positive and negative amounts I have used an 'ABS' field to calculate the absolute value so that I can filter on variances over a certain amount either way (using the standard filter function 'at least 350').
In my workbook the first sheet is unfiltered. The second sheet is what I currently have, which is filtered on amounts over 350 but without the 'Other' field.
The third sheet is my attempt at adding the 'Other' field into the table but as you can see it has not worked as expected.
Am I doing something wrong somewhere with the LOD calc?
Any help is greatly appreciated.
Thanks,
Dave R

Test 2 Dave R.twbx 64.1 KB


3. Re: Show Filtered Items as Sum Total in Table
Michel Caissie Mar 14, 2017 10:20 AM (in response to David Roscoe)Dave,
You can check on Sheet 4 if you get the expected values.
First, I suggest that you FIXED the cost and then making other calculations, instead of making calculations with the cost and then FIXING the results.
Here in your view, you have two field dimensions , so you have to include both in the lod.
Also, since for each Field1 / Field2 group, you need the cost for each period, I would go with calculations like
{FIXED [Field1],[Field2]:SUM( if Period = 'Period1' then [Cost1] end )}
Once, you have your 3 costs, you can compute the Variances
[Fields(Cost)(Period1)]

[Fields(Cost)(Period2)]

Test 2 Dave R. (ed mc)twbx.twbx 82.5 KB


4. Re: Show Filtered Items as Sum Total in Table
David Roscoe Mar 14, 2017 11:41 AM (in response to Michel Caissie)This is brilliant, thanks Michel that has worked & is exactly what I needed.
Thanks for all of your help with this!
Regards
Dave