Answered by:
SSRS Expression to calculate percentage with the denominator being the first value in the same column group in a matrix report

Hello, Expert,
Could you please help me with the following problem? Thanks in advance!
I have designed the matrix report (SSRS 2012) with database table structure looks like below.
Week
Version
Count
Week1
V1.1
4000
Week1
V1.2
4000
Week1
V1.3
4000
Week2
V1.1
3000
Week2
V1.2
3000
Week2
V1.3
3000
Week3
V1.1
2000
Week3
V1.2
2000
Week3
V1.3
2000
Week4
V1.1
1000
Week4
V1.2
1000
Week4
V1.3
1000
The matrix report displays like below. Column grows based on no of Week in the table.
Week1
Week2
Week3
Week4
V1.1
4000
3000
2000
1000
V1.2
4000
3000
2000
1000
V1.3
4000
3000
2000
1000
What I want to do now is: instead of the Count values, I need to calculate the percentage of each week over the Count in Week1 so that each cell displays Count(Week2)/Count(Week1), Count(Week3)/Count(Week1), etc. What is the SSRS expression for that? I’m new to SSRS, Please help!
Question
Answers

Hi QQFA,
To achieve your goal, please refer to the expression below:
=iif(Fields!Week.Value="Week1",Fields!Count.Value, FormatPercent(Fields!Count.Value/First(Fields!Count.Value,"Version"),0))
If we add the above expression in the matrix control, we can get the screenshots as shown below:
Hope this helps.
Regards,
Heidi DuanHeidi Duan
TechNet Community Support Proposed as answer by SathyanarrayananSModerator Wednesday, January 01, 2014 12:08 PM
 Marked as answer by HeidiDuanMicrosoft contingent staff, Moderator Tuesday, January 07, 2014 9:56 AM

Hi QQFA,
Thanks for your posting.
This is a relative thread for your reference. http://social.msdn.microsoft.com/Forums/sqlserver/enUS/869778682c29435cb7c610eca231ef79/ssrsexpressioncomparecolumnstofirstcolumningroup?forum=sqlreportingservices .
Hope this helps.
Regards,
Heidi DuanHeidi Duan
TechNet Community Support Marked as answer by HeidiDuanMicrosoft contingent staff, Moderator Wednesday, January 15, 2014 6:50 AM
All replies

Hi QQFA,
To achieve your goal, please refer to the expression below:
=iif(Fields!Week.Value="Week1",Fields!Count.Value, FormatPercent(Fields!Count.Value/First(Fields!Count.Value,"Version"),0))
If we add the above expression in the matrix control, we can get the screenshots as shown below:
Hope this helps.
Regards,
Heidi DuanHeidi Duan
TechNet Community Support Proposed as answer by SathyanarrayananSModerator Wednesday, January 01, 2014 12:08 PM
 Marked as answer by HeidiDuanMicrosoft contingent staff, Moderator Tuesday, January 07, 2014 9:56 AM

Heidi,
Thank you so much for your reply and solution! Now I know how to get the first field in a group. But I can't apply your solution directly just yet because in order to explain my problem I simplified my data structure. My real data structure has a nested group like this:
Week VersionMajor VersionMinor Count
   
Week1 V1 V1.1 2000
Week2 V1 V1.1 1500
Week3 V1 V1.1 800
Week1 V1 V1.2 1000
Week2 V1 V1.2 500
Week3 V1 V1.2 200
Week1 V1 V1.1 2000
Week2 V1 V1.1 1500
Week3 V1 V1.1 800
Week1 V1 V1.2 1000
Week2 V1 V1.2 500
Week3 V1 V1.2 200
Here is the query for the above data:
SELECT 'Week1' AS Week, 'V1' AS VersionMajor, 'V1.1' AS VersionMinor, 2000 AS Count
UNION ALL
SELECT 'Week2' AS Week, 'V1' AS VersionMajor, 'V1.1' AS VersionMinor, 1500 AS Count
UNION ALL
SELECT 'Week3' AS Week, 'V1' AS VersionMajor, 'V1.1' AS VersionMinor, 800 AS Count
UNION ALL
SELECT 'Week1' AS Week, 'V1' AS VersionMajor, 'V1.2' AS VersionMinor, 1000 AS Count
UNION ALL
SELECT 'Week2' AS Week, 'V1' AS VersionMajor, 'V1.2' AS VersionMinor, 500 AS Count
UNION ALL
SELECT 'Week3' AS Week, 'V1' AS VersionMajor, 'V1.2' AS VersionMinor, 200 AS Count
UNION ALL
SELECT 'Week1' AS Week, 'V1' AS VersionMajor, 'V1.1' AS VersionMinor, 2000 AS Count
UNION ALL
SELECT 'Week2' AS Week, 'V1' AS VersionMajor, 'V1.1' AS VersionMinor, 1500 AS Count
UNION ALL
SELECT 'Week3' AS Week, 'V1' AS VersionMajor, 'V1.1' AS VersionMinor, 800 AS Count
UNION ALL
SELECT 'Week1' AS Week, 'V1' AS VersionMajor, 'V1.2' AS VersionMinor, 1000 AS Count
UNION ALL
SELECT 'Week2' AS Week, 'V1' AS VersionMajor, 'V1.2' AS VersionMinor, 500 AS Count
UNION ALL
SELECT 'Week3' AS Week, 'V1' AS VersionMajor, 'V1.2' AS VersionMinor, 200 AS CountAnd my matrix table looks like this:
What I need to do is to calculate the percentage of each week over week1 for
1) V1 Total
2) V1.1
3) V1.2
I can't use your solution directly because I need to apply Sum function for each cell and the Sum function gives me error when I try to use it on the denominator First(Fields!Count.Value,"VersionMinor"). If you could help me further to get to the final solution, I'd really appreciate it!!
Happy New Year!

Hi QQFA,
Thanks for your posting.
This is a relative thread for your reference. http://social.msdn.microsoft.com/Forums/sqlserver/enUS/869778682c29435cb7c610eca231ef79/ssrsexpressioncomparecolumnstofirstcolumningroup?forum=sqlreportingservices .
Hope this helps.
Regards,
Heidi DuanHeidi Duan
TechNet Community Support Marked as answer by HeidiDuanMicrosoft contingent staff, Moderator Wednesday, January 15, 2014 6:50 AM