I'm using SSRS and have made a "pivot table". The pivot table has two calculated columns, and I want to use those values to get a third value, the difference between both values.
This is an example of what my table looks like:
*Edit: the percentages are averages of about 40+ values with those row/column attributes *
Category A B Difference
1 47% 57% B-A
2 45% 49% B-A
3 13% 73% B-A
4 27% 23% B-A
Total 23% 34% B-A
Each percent is an average of the category grouped by A and B. I need a function that will gather the average for A and subtract it from the Average of B for that row. I've tried the following so far and have resulted in error:
=IIf(Fields!Log1.Value = "End", Avg(Fields!Log1.Value), Nothing) - IIf(Fields!Log1.Value = "Begin", Avg(Fields!Log1.Value), Nothing)
Any help is appreciated. Thanks
I believe the issue here has to do with how you are referencing the Log1 column within the Category group. Think about how the data looks behind the scenes. Within each category you have multiple rows that you want to summarize. Some rows might have a different value for Log1. The intent is to get the average of the rows that have a certain Log1 value.
What's actually happening is that it's arbitrarily picking the first Log1 value within each group and using that in the expression. In other words, you are getting the average of all the rows or none of the rows for that group based on what Log1 happened to be in the first row of the category group.
As a side note, in your expression, you refer to Log1 as a text field and in the Avg function, I'm assuming those should actually be two different columns and you're trying to average numbers in the A and B columns.
What we actually want it to do is check the value of Log1 for every row and then average those values. So, here's what I suggest:
Create two calculated fields on your dataset. Use expressions like this:
=IIf(Fields!Log1.Value = "Begin", Fields!ColumnA.Value, Nothing)
=IIf(Fields!Log1.Value = "End", Fields!ColumnB.Value, Nothing)
In column A of your table refer to this new calculated field. Repeat for column B.
=Avg(Fields!NewColA.Value)
To get the difference:
=Avg(Fields!NewColB.Value) - Avg(Fields!NewColA.Value)
In other words, we are forcing it to filter values row-by-row and then aggregating those filtered values.