SSRS Average on two columns

Question

SSRS Average on two columns

sindhupavani123 50704

SSC-Addicted

Points: 415
More actions
May 10, 2016 at 7:50 am

#310478

I have data in SSRS report that looks something like below:
IDSellerHyderabadBangalore
1Nikhil23
1Nikhil12
1Nikhil24
1Nikhil31
2Hema33
2Hema21
2Hema42
A seller named Nikhil sells 2 products in Hyderabad, 3 in Bangalore and 4 in Mysore
, the next day he sells 1 in Hyderabad,2 in Bang and 5 in Mysore, and so on.
I would like to get the average of products per id and seller i.e
the expected data should look like this
ID Seller Hyderabad Bangalore
1Nikhil SUM OF HYDERBAD
PRODUCTS/ COUNT BY
NIKHIL SUM OF Banglore PRODUCTS/COUNT BY NIKHIL
i.e 2+1+2+3/4= 2(AVG) 3+2+4+1/4
2Hema SUM OF HYDERBAD
PRODUCTS/ COUNT BY HEMA SUM OF Banglore PRODUCTS/COUNT BY HEMA
i.e 3+2+4/3= 2(AVG) 3+1+2/3
How can I achieve this average in SSRS?
Can somebody help on this please?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

Add a total's row to you table. You can do this by right clicking one of the cells with your data in and clicking Add Total. SSRS will automatically add a new row at the bottom of your dataset.

Click the newly added cell at the button of your Column you want to average and click the little symbol on the right of the cell. Select the field you would like the average. The cell should populate with something like "[Sum(Bangalore)]". Right click the cell again and click Expression, and change the "Sum" to "Avg" so the expression becomes "=Avg(Fields!Bangalore.Value)" (you can actually go straight to this step if you want and just type this in) and click ok.

Done!

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sindhupavani123 50704 SSC-Addicted Points: 415 More actions · Answer 2

Hi Thom,

Thanks for your reply!

But my question is not averaging on a single column, if I understand it correct,

The data in report is something like this

IDSeller HyderabadBangalore

1Nikhil 2 3

1Nikhil 1 2

1Nikhil 2 4

1Nikhil 3 1

2Hema 3 3

2Hema 2 1

2Hema 4 2

SO i have to average on ID= 1 Seller= Nikhil Hyderabad=sum on products sold by nikhil/count of products sold by Nikhil and its the same for Bnagalore.

So, the expected output should be

ID Seller Hyderabad Bangalore

1 Nikhil 8/4=2 10/4=2.5

2 Hema 9/3=3 6/3=2

And average should be done only by using SSRS?

Thanks in advance!

pietlinden SSC Guru Points: 63425 More actions · Answer 3

pietlinden

SSC Guru

Points: 63425

May 10, 2016 at 11:28 am

#1877904

do you have the unpivoted data somewhere?

sindhupavani123 50704 SSC-Addicted Points: 415 More actions · Answer 4

I have a report as input which should be modified , there is no unpivoted data.

pietlinden SSC Guru Points: 63425 More actions · Answer 5

After looking at this and playing with it for a little while, I stand by my original conclusion - you need the UNsummarized data from somewhere. If you can talk to the developers, have one write a simple stored procedure for you that returns the columns you need and then use a Matrix in your report and change the aggregate from SUM to AVERAGE, and you're finished.

Salesperson goes on ROWS and City goes on COLUMNS.

Then this is as close to automatic as it gets.

Thom A SSC Guru Points: 99126 More actions · Answer 6

sindhupavani123 50704 (5/10/2016)
Hi Thom,
Thanks for your reply!
But my question is not averaging on a single column, if I understand it correct,
The data in report is something like this
IDSeller HyderabadBangalore
1Nikhil 2 3
1Nikhil 1 2
1Nikhil 2 4
1Nikhil 3 1
2Hema 3 3
2Hema 2 1
2Hema 4 2
SO i have to average on ID= 1 Seller= Nikhil Hyderabad=sum on products sold by nikhil/count of products sold by Nikhil and its the same for Bnagalore.
So, the expected output should be
ID Seller Hyderabad Bangalore
1 Nikhil 8/4=2 10/4=2.5
2 Hema 9/3=3 6/3=2
And average should be done only by using SSRS?
Thanks in advance!

Can you provide an example dataset of how you want the final result set to look, and a of your initial dataset as well. It would be far easier if you could provide them in tables, with just the figures in, don't worry about the maths/forumlas your trying to achieve. I think I know what you're aiming at now, and you can achieve this, but I'd rather not write a lengthy post and miss what you're aiming at 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

David Burrows SSC Guru Points: 65138 More actions · Answer 7

Create a new report, add data source and dataset

Drag all four columns (ID Seller Hyderabad Bangalore) to detail box

continue on to finish report

Double click on Details_Group under Row Groups

Click Add to add group expression and select ID

Change expression for Hyderabad to

=Avg(Fields!Hyderabad.Value)

Change expression for Bangalore to

=Avg(Fields!Bangalore.Value)

Voila!

Far away is close at hand in the images of elsewhere.
Anon.

sindhupavani123 50704 SSC-Addicted Points: 415 More actions · Answer 8

Thank you SSC Veteran! I wrote a Stored procedure to get the columns I need in a table and that worked out! Have used the SP table data to get what is needed for my end-user

Thanks for the solution!