Calculate the average in SSRS 2016

0
votes

I am using SSRS 2016 application to pull data from SharePoint list.
My task is to calculate the average DaysUsed and the average Percentage from a column.

It is a Matrix report which has two columns:

  • Sum(Fields!Days.Value)
  • Sum(Fields!Percentage.Value)

The task is to show the average days and average percentage.
I have tried the =Avg(Fields!Days.Value) but this shows wrong result.

enter image description here

UPDATE

Yes, the matrix is grouped by the Month to show each relevant month.
See the screenshot below what I have tested so far.

enter image description here

The DaysUsed is a calculated between 2 fields [FromDate] and [UntilDate] by summarising the total days minus the weekends. see below:

=(DateDiff(DateInterval.day,CDate(format(Fields!FromDate.Value,"MM-dd-yyyy")), CDate(format(Fields!UntilDate.Value,"MM-dd-yyyy")))+1)
- (DateDiff(DateInterval.WeekOfYear,CDate(format(Fields!FromDate.Value,"MM-dd-yyyy")), CDate(format(Fields!UntilDate.Value,"MM-dd-yyyy")))*2) 
- IIF(Weekday( CDate(format(Fields!FromDate.Value,"MM-dd-yyyy")),1) = 1,1,0)
- IIF(Weekday( CDate(format(Fields!FromDate.Value,"MM-dd-yyyy")),1) = 7,1,0)
- IIF(Weekday( CDate(format(Fields!UntilDate.Value,"MM-dd-yyyy")),1) = 1,1,0)
- IIF(Weekday( CDate(format(Fields!UntilDate.Value,"MM-dd-yyyy")),1) = 7,1,0)

Furthermore, the [DaysUsed] row then consists of IIF(value is null then show 0 otherwise show the total value of days used).

=IIF(IsNothing(Sum(Fields!Days.Value)), "0", Sum(Fields!Days.Value) )
2
reporting-servicesssrs-2016
shall your AvgDays not be 34/6=5.66 and 75/6=12.5 ??? – AnkUser
@AnkUser that is correct :) I fail to make the SSRS say "use Sum(....) / Count(...) but I obviously not good at this :) – Bucki
No worries I guess you got that now. I will write down the answer would be great if you mark it. Shall be good to earn some points – AnkUser

2 Answers

0
votes
Sum(Fields!Days.Value)/CountRows()

Shall give you average days. Similarly goes for others as well.

0
votes

If you has a field called Fields!Days.Value, I guess your data in the screenshot is already grouped by month. This means the expression

=Avg(Fields!Days.Value)

gives you the average over all Days (sum of all days / count of all days).

If you want to use the grouped values (by mounth) you either can use the following expression when you are inside the scope (the monthly socpe; indicated by the brackets on the most left side on your tablix)

=Sum(Fields!Days.Value) / Count(Fields!Days.Value)

If you are not in the scope you have to tell the tablix that is should use your monthly grouping. For this you can use the following:

=Sum(Fields!Days.Value, "YourMonthGroupName") / Count(Fields!Days.Value, "YourMonthGroupName")