Hi Sri,
Here's what you need to do:
1. Create a formula (@DC) with this code:
DistinctCount({@MonthYear},{@Year})
I believe 'Year' and 'Monthyear' are the columns in your crosstab?
2. Go to the Crosstab Expert and add this DC formula field as the second summary. Set its summary function as 'Maximum'.
3. Go to the Preview tab and right-click the 'Total' cell header and select 'Insert Child'.
4. A new blank column will be added with zero values at the end of the first year on your crosstab.
5. Right-click this blank header cell and select 'Calculated Member' > Edit Insertion formula and use this code:
GetColumnGroupIndexOf(CurrentColumnIndex) = 1
6. Right-click the blank white space to the top left of the crosstab and select Advanced Calculations > Calculated Member.
7. In the 'Properties' section to the right, for 'Group Level', select 'Before'.
8. You should now see the blank column for ALL years beside the original "Total" column.
9. Right-click this blank columns header cell > Calculated Member > Edit Header formula > Type in:
"Total"
10. In this new "Total" column, you should see two summary cells with zero values one below the other.
11. Right-click the Second summary cell in this Total Column > Calculated Member > Edit Calculation Formula > use this code:
GetTotalValueFor("@MonthYear",1
12. Right-click the First summary cell in this Total Column > Calculated Member > Edit Calculation Formula > use this code:
If GetTotalValueFor("@MonthYear",1) = 0 then 0 else GetTotalValueFor("@MonthYear",0)/GetTotalValueFor("@MonthYear",1)
13. Highlight the Second Summary cell and change its text colour to White.
14. Reduce the height of this second summary cell as small as possible.
15. Go to the Crosstab Expert > Customize Style tab > Under "Columns", highlight "Year".
Under "Group Options", select "Suppress Subtotal"
Let me know how this goes.
-Abhilash
Hi Sriram,
What totals do you get? In the screenshot, your example in red is correct for 2017 but 2016 should be 94.95.
Also, your example shows 2017 rounded to a whole number but 2016 to a decimal. Your formula looks like it is not rounding.
Can you show us what numbers you expect to see for 2017 and 2016?
Thanks,
Brian
Hi Sri,
Adding a second summary does not insert a new row.
Could you check if you've inserted a Calculated Row as well?
Right-click the blank white space to the top left of the crosstab > select Advanced Calculations > Calculated Member.
Under 'Calculated Members', how many entries do you see?
-Abhilash
Add comment