Rank: Advanced Member
Groups: extranet\Forum
Joined: 8/19/2014(UTC) Posts: 109

I have two columns in my crosstab and I need to do a calculation which involves the two columns and I want to show only the total of the calculated column, not the individual rows.
Column names A and B.
Calculated column = ((∑ Column B)  (∑ Column B where value in column A = 0)) / (∑ Column A *1000). And I want to show this in the total row at the top of my crosstab next to my other totals.
Is this possible?




Rank: Administration
Groups: extranet\Forum
Joined: 4/7/2011(UTC) Posts: 503

The following is based on the assumption that your two columns are made up from two dimension members or from two comparison elements. If they are instead two different measures, the formulas will need to be modified. Add a calculated column: sum(d2, 0, m1) * if sum(d1, 0, m1) <> 0 then 1 else 0Add another calculated column: sum(c1, all(c), m1) / sum(d1, all, m1) * 1000The last calculated column should produce the desired result in the Total row and zeros in the other rows. BR / Ole Edited by user Wednesday, August 27, 2014 12:08:36 PM(UTC)
 Reason: Not specified 
Ole Dyring 



Rank: Advanced Member
Groups: extranet\Forum
Joined: 8/19/2014(UTC) Posts: 109

od@targit.com wrote:The following is based on the assumption that your two columns are made up from two dimension members or from two comparison elements. If they are instead two different measures, the formulas will need to be modified.
Add a calculated column: sum(d2, 0, m1) * if sum(d1, 0, m1) <> 0 then 1 else 0
Add another calculated column: sum(c1, all(c), m1) / sum(d1, all, m1) * 1000
The last calculated column should produce the desired result in the Total row and zeros in the other rows.
BR / Ole Thanks. I think I've done something wrong when modifying the code you supplied. My column references are these; Column B : (d1,0,m1) Column A: (d1,0,m2)




Rank: Administration
Groups: extranet\Forum
Joined: 4/7/2011(UTC) Posts: 503

Ok. So your two columns are in fact two different measures. In that case the formulas should be modified to: Add a calculated column: sum(d1, 0, m1) * if sum(d1, 0, m2) <> 0 then 1 else 0Add another calculated column: sum(c1, all(c), m2) / sum(d1, all, m2) * 1000BR / Ole Edited by user Wednesday, August 27, 2014 2:00:05 PM(UTC)
 Reason: Not specified 
Ole Dyring 



Rank: Advanced Member
Groups: extranet\Forum
Joined: 8/19/2014(UTC) Posts: 109

od@targit.com wrote:Ok. So your two columns are in fact two different measures.
In that case the formulas should be modified to:
Add a calculated column: sum(d1, 0, m2) * if sum(d1, 0, m1) <> 0 then 1 else 0
Add another calculated column: sum(c1, all(c), m1) / sum(d1, all, m1) * 1000
BR / Ole
The first calculated column, with this code applied, becomes just a replica of my column B. Is that the intention? If I understand your code correctly the first calculated column should have values 1 or 0?




Rank: Administration
Groups: extranet\Forum
Joined: 4/7/2011(UTC) Posts: 503

The first calculated column should replicate B only in rows where A is a value different from zero. In rows where A is zero, the first calculated column should also show zero.
If possible, please attach a screenshot of your table. 
Ole Dyring 



Rank: Advanced Member
Groups: extranet\Forum
Joined: 8/19/2014(UTC) Posts: 109

od@targit.com wrote:The first calculated column should replicate B only in rows where A is a value different from zero. In rows where A is zero, the first calculated column should also show zero.
If possible, please attach a screenshot of your table. Thanks, I figured it out now. #1 if sum(d1, 0, m2) > 0 then sum(d1, 0, m1) else 0 #2 sum(c1, all(c), m1) / (sum(d1, all, m2) * 1000)




Rank: Administration
Groups: extranet\Forum
Joined: 4/7/2011(UTC) Posts: 503


Ole Dyring 



You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.