erp1022 asked on # calculating weighted averages on an SRS report

Hello,

I have been around in circles with this and cannot figure this out. I am trying to get the weighted average on a sql report based on a count and an amount.

So first I get the average price of something we sell by creating an expression: Price/Count

(it will not let me do a calc field because I am using the count aggregate function)

We only sell 4 major items, which I am basing my report on. So I know that the next step is to take each of my item's average price, multiply it by the count for that item, add up those totals, then divide by the total count.

I have tried variations on this expression:

=(SUM((Fields!CRDTAMNT.Value/COUNT(Fields!ACCOUNT.Value))* COUNT(Fields!ACCOUNT.Value)))/COUNT(Fields!ACCOUNT.Value)

(Account is the count) and I keep getting errors about nested aggregate functions.

Does anyone know how to do this?

Thanks!

I have been around in circles with this and cannot figure this out. I am trying to get the weighted average on a sql report based on a count and an amount.

So first I get the average price of something we sell by creating an expression: Price/Count

(it will not let me do a calc field because I am using the count aggregate function)

We only sell 4 major items, which I am basing my report on. So I know that the next step is to take each of my item's average price, multiply it by the count for that item, add up those totals, then divide by the total count.

I have tried variations on this expression:

=(SUM((Fields!CRDTAMNT.Val

(Account is the count) and I keep getting errors about nested aggregate functions.

Does anyone know how to do this?

Thanks!

SSRSMicrosoft SQL ServerMicrosoft SQL Server 2008

erp1022

Not sure what you mean by using a Tablix Control, so not sure whether or not I'm using it. I am displaying our items by selecting them from an embedded query. Do you need to see that?

When you say a sample of the report, what exactly are you looking for? the actual rdl file?

Please see attachment, this is a screenshot and has some explanations of what I'm trying to achieve.

Weighted-Avg.docx

When you say a sample of the report, what exactly are you looking for? the actual rdl file?

Please see attachment, this is a screenshot and has some explanations of what I'm trying to achieve.

Alfred A.

You are using a Tablix (Table) Control. OK, I don't know if I understand it correctly but it looks like you have an error in your equation.

1. You mention that Average Acquisition price is

=(Sum(Fields!CRDTAMNT.Value))/(COUNT(Fields!ACCOUNT.Value))

2. Then, you multiply the count of each item against the average acquisition price and then divide by total count.

=(((Sum(Fields!CRDTAMNT.Value))/(COUNT(Fields!ACCOUNT.Value))) * (COUNT(Fields!ACCOUNT.Value)) / COUNT(Fields!ACCOUNT.Value)

There is definitely wrong with this one as it will just cancel out the multiplied and divided.

OK. Why don't you just focus on the "Total" row for your weighted average? I guess you added the totals using "Add Total". You can just apply =(Sum(Fields!CRDTAMNT.Value))/(COUNT(Fields!ACCOUNT.Value)) under the totals (expression field you are after) of the Average Acquisition column. The values for the fields at this point are the values in the fifth row of your report (total row).

I just hope this would help and I understand what is your problem really are.

1. You mention that Average Acquisition price is

=(Sum(Fields!CRDTAMNT.Valu

2. Then, you multiply the count of each item against the average acquisition price and then divide by total count.

=(((Sum(Fields!CRDTAMNT.Va

There is definitely wrong with this one as it will just cancel out the multiplied and divided.

OK. Why don't you just focus on the "Total" row for your weighted average? I guess you added the totals using "Add Total". You can just apply =(Sum(Fields!CRDTAMNT.Valu

I just hope this would help and I understand what is your problem really are.

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

Alfred A.

Also, check the following link for further details on Calculating Totals

http://msdn.microsoft.com/en-us/library/bb630415%28SQL.100%29.aspx

http://msdn.microsoft.com/en-us/library/bb630415%28SQL.100%29.aspx

erp1022

What I need to calculate is the *WEIGHTED AVERAGE*. It's not a mathematical average, which would be to total everything and divide by some number. For a weighted average you have to multiply each number by the value specific to that number, add those totals, then divide by the total value.

Based on what I described in my above emails and the screen shot I added, does anyone know how to do this in the SSRS report interface or how to code it in my query?

Based on what I described in my above emails and the screen shot I added, does anyone know how to do this in the SSRS report interface or how to code it in my query?

Alfred A.

Actually, now that you mention about your query, why don't you apply all the calculations outside of SSRS and do it SQL Server 2008?

The way I do things is that my data warehouse consist of actual transformed values from the original OLTP (production) database. All my complex calculations are done at the transform level. And then in my SSRS report, I just access the resulting dataset straightforward without any complex expressions. Of course, take note that there will still be expressions to be used but mostly at a simple level.

Also I understand you are trying to get the weighted average but it is not really clear. Do you have any sample Preview of your report?

OK. Correct me if I am wrong based on my sample data below. Note that these are sample values based on your columns

Item1 5 600 [Sum[SvcFee] 600/5

Item2 8 500 "" 500/4

Item3 7 400 "" 400/7

Item4 4 300 "" 300/4

Total 24 1800 (600/5 + 500/4 + 400/7 + 300/4) / 4

OK. What did I missed above? Note that I know what you are trying to do but I need to understand your data! Please provide more info.

The way I do things is that my data warehouse consist of actual transformed values from the original OLTP (production) database. All my complex calculations are done at the transform level. And then in my SSRS report, I just access the resulting dataset straightforward without any complex expressions. Of course, take note that there will still be expressions to be used but mostly at a simple level.

Also I understand you are trying to get the weighted average but it is not really clear. Do you have any sample Preview of your report?

OK. Correct me if I am wrong based on my sample data below. Note that these are sample values based on your columns

Item1 5 600 [Sum[SvcFee] 600/5

Item2 8 500 "" 500/4

Item3 7 400 "" 400/7

Item4 4 300 "" 300/4

Total 24 1800 (600/5 + 500/4 + 400/7 + 300/4) / 4

OK. What did I missed above? Note that I know what you are trying to do but I need to understand your data! Please provide more info.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Alfred A.

Oh by the way, the items in my sample data in my previous post are grouped items (i.e. Item1, etc.).

erp1022

I have attached a screen shot of the preview of my report, but I don't think that will help. I can't get any weighted average expressions to work, so now it just displays as 'Error'

What would the code/syntaxt be in SQL to calculate a weighted average?

You asked me for more information, but I have no idea what you are looking for so please let me know specifically what you need.

Weighted-Avg-2.docx

What would the code/syntaxt be in SQL to calculate a weighted average?

You asked me for more information, but I have no idea what you are looking for so please let me know specifically what you need.

Alfred A.

Yes. This is more like it. Based on your sample report, what is your expected weighted average result when calculated manually? I can see in your sample report that it displays "Error".

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a questionAlfred A.

Well, when I ask clarification, other experts will see this as well. So, it would not only help me understand your problem but others as well. Obviously, people would ask questions in order to clarify issues. Also, other experts are anytime free to comment if they wish. You have to understand, this is not about being unsure or not knowing what to do but trying to understand the underlying problem.

Anyway, I do hope you resolve your problem as soon as possible and I will stop at this point and let others help you if you wish. Goodluck!

Anyway, I do hope you resolve your problem as soon as possible and I will stop at this point and let others help you if you wish. Goodluck!

Alfred A.

@erp1022,

For the record, it is not that I don't know how to answer, you mentioned to let someone else step in, so I stopped!

For your information, I am not being paid to help you and if you are trying to be personal on this, you are very unprofessional!

For the record, it is not that I don't know how to answer, you mentioned to let someone else step in, so I stopped!

For your information, I am not being paid to help you and if you are trying to be personal on this, you are very unprofessional!

erp1022

It seems as though person who is responding does not know the answer. It's a simple straighforward question on how to code a calculated field in sql.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

=(SUM((Fields!CRDTAMNT.Val

Can you provide a sample of the SQL report you are trying to work on, if possible, so that we can visualized what you are trying to do?