Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
malachi151
Posting Yak Master
152 Posts |
 Posted - 2012-12-07 : 14:52:30
|
| 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
4 Posts |
Posted - 2013-01-01 : 12:23:59
|
| 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. |
 |
|
|
|
|
|
Weighted Averages in SSAS?