SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Weighted Averages in SSAS?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

malachi151
Posting Yak Master

148 Posts

Posted - 12/07/2012 :  14:52:30  Show Profile  Visit malachi151's Homepage  Reply with Quote
I'm been trying to figure out how to create a weighted average as a calculated measure in a SSAS cube, and thus far I don't see any good solutions, which seems kind of surprising...

Am I just missing something here?

For a weighted average in SQL I would do SUM(value*weight)/SUM(weight)

or in other words:
SUM(V1*W1:V2*W2:V3*W3)/SUM(W1:W3)

The only suggestion I've seen on how to do this in SSAS is to create the weighted values in the source table in the DSV, however this may be okay for a small cube with a very limited number of measures and weights, but is very impractical on a larger cube, especially when some of the things that I want weighted averages of are themselves calculations in the cube.

So, is there a way to do this or not?

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware

Matengele
Starting Member

Zambia
4 Posts

Posted - 01/01/2013 :  12:23:59  Show Profile  Reply with Quote
Hi ,
on the cube structure tab of your analysis services cube, add a new measure and select no aggregation for the usage. select the fields you want to add to your report provided they belong to one of the tables included in your data source view. You can then go to the calculations tab and click new calculated messure, on the messure expression drag the fields you want to create an average from and add the necessary mathematical operators. Hope this helps.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000