Grand Total not correct..

Hey guys,

got a strange problem happening and wondering if anyone can help me..

I have added a calculated member which calculates an amount from a percentage of sales. Only certain customers get this rebate..

([Measures].[Sales Amount] / 100) * [Measures].[DMF Percentage]

When i run the report., the calculation works, but the grand total does not add up at all.. See Below: (sorry for the long post)

The total it is showing is $2,888,196.60, where if i add up all the individial ones, the total is $60,393.99

Can anyone help me with this problem?

Thanks

Scotty

2007 Q1 P1

Sales Amount

DMF Value

$10.03

$6,453.16

$3,097.86

$406.85

$721.00

$343,758.92

$8,593.97

$9,898.78

$757.04

$3,243.78

$2,996.92

$301.00

$5,104.94

$102.10

$612.46

$948.40

$21.36

$78.15

$0.00

$23,609.67

$708.29

$28,562.25

$1,664.70

$18,877.30

$53,328.95

$1,599.87

$11,048.06

$73,165.96

$2,194.98

$48,843.92

$1,465.32

$75.06

$54,544.81

$1,636.34

$22,595.68

$677.87

$1,038.74

$319.90

$780.57

$20,278.27

$202.78

$91.94

$299.45

$84.29

$5,193.40

$44,062.76

$5,516.30

$269.04

$2,206.00

$2,115.15

$4,807.06

$5,807.60

$3,885.50

$145.07

$1,412.26

$7,612.20

$753.50

$18,131.96

$126,928.37

$44,628.20

$52.95

$2,076.93

$150.80

$73,777.80

$106,490.34

$2,129.81

$38,891.58

$31,473.10

$944.19

$116,537.06

$18,251.76

$547.55

$1,270.00

$2,737.50

$443.23

$131.07

$333.50

$98,267.33

$2,948.02

$58,050.00

$1,741.50

$59,816.85

$1,794.51

$1,843.65

$19,529.77

$585.89

$127,937.43

$3,838.12

-$2,208.12

$7,409.64

$12,571.79

$1,168.00

$3,029.21

$914.49

$4,390.07

$934.88

$230.77

$11,167.58

$2,403.23

$11,656.50

$20,722.90

$4,287.29

$148,204.76

$5,187.17

$1,068.91

$37.37

$268.86

$418.76

$4,549.74

$45.50

$2,541.82

$25.42

$11,324.19

$113.24

$430.06

$4.30

$1,871.75

$1,708.40

$8,314.29

$83.14

$28,158.40

$281.58

$310.80

$9,790.22

$293.71

$24,653.49

$246.53

$231.57

$901.84

$271.56

$233.03

$1,339.37

$164.38

$141,002.09

$2,820.04

$6,602.88

$132.06

$5,248.72

$52.49

$189.22

$1.89

$582.41

$397.70

$3,418.89

$5,243.68

$52.44

$6,407.74

$64.08

$6,307.73

$63.08

$15,741.19

$157.41

$3,876.07

$38.76

$23,880.87

$716.43

$0.00

$1,228.00

$1,522.00

$9,931.88

$47,516.31

$950.33

$33,437.79

$668.76

$35,417.15

$708.34

$495,086.89

$14,852.61

$4,212.05

$126.36

$369.00

-$35.71

-$1.07

$880.00

$6,475.46

$2,364.96

$73.64

$49,913.90

$998.28

$6,612.57

$4.05

$3,008,538.12

$2,888,196.60

A calculated measure won't automatically aggregate like a regular measure. The MDX expression could be re-written to roll up from individual customers. Could you describe [Measures].[DMF Percentage] - if it is a regular cube measure on a different measure group than [Sales Amount], you might be able to create a new cube measure, using a Measure Expression?|||

Hey Deepak,

Thanks for the explantion.

the dmf percantage is just a regular cube measure from a different measure group (DMF)

I would be thankful if you could explain how to create a new cube measure using a measure expression..

thanks a lot,,

Scotty

|||

Create a named calculation on the [Sales Amount] fact table like [DMF Value]: [Sales Amount] / 100. Then add a new "sum" measure: [DMF Value] on this named calculation field, defining its Measure Expression as: [Measures].[DMF Value] * [Measures].[DMF Percentage].

|||

Deepak,

thanks for the hand.. You are all over SSAS obviously an expert with it.. I am having one more problem that i posted last week and no one has been able to help. Would you mind looking at the post below and see if you have any idea?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1175605&SiteID=1

Much appriciated.

Thanks

Scotty

Good books about huge databases ?

Hey
I need some good books about working with enormous amount of data. The table
rows are counted in milions. Have you used SS-2005 with such big amount of
data ? What was your experiences ? Any bad things happend ? Any supprises ?
Any bugs ? Is there big perfomance diffrence between SS-2000 and 2005 ?
What books will you recommend to read to get to know some nice techniques ?
What articles ? What websites ? Maybe there is some forum for developers /
programmers for such databases ?
Please share your experience :)
JarodSQL Server 2005 is really nice...
more functions
more speed
more realibility
more services
etc...
I'm reading from Wrox Professional ADO.NET 2: Programming with SQL Server
2005, Oracle and MySQL
it's a generalist book but they have more and only for SQL Server 2005
check them out: http://www.wrox.com/WileyCDA/Section/id-131384.html
and you can always buy it from amazon.com or amazon.co.uk where I buy my
books from Wrox
Bruno Alexandre
(a Portuguese in Kbenhanv, Danmark)
"Jarod" <blueice@.NOSPAM.gazeta.pl> escreveu na mensagem
news:%235eJAvUnGHA.4164@.TK2MSFTNGP05.phx.gbl...
> Hey
> I need some good books about working with enormous amount of data. The
> table rows are counted in milions. Have you used SS-2005 with such big
> amount of data ? What was your experiences ? Any bad things happend ? Any
> supprises ? Any bugs ? Is there big perfomance diffrence between SS-2000
> and 2005 ?
> What books will you recommend to read to get to know some nice techniques
> ? What articles ? What websites ? Maybe there is some forum for developers
> / programmers for such databases ?
> Please share your experience :)
> Jarod|||Jarod wrote:
> Hey
> I need some good books about working with enormous amount of data. The
> table rows are counted in milions. Have you used SS-2005 with such big
> amount of data ? What was your experiences ? Any bad things happend ?
> Any supprises ? Any bugs ? Is there big perfomance diffrence between
> SS-2000 and 2005 ?
> What books will you recommend to read to get to know some nice
> techniques ? What articles ? What websites ? Maybe there is some forum
> for developers / programmers for such databases ?
> Please share your experience :)
> Jarod
I'm currently reading "The Art Of SQL"
(http://www.amazon.com/gp/product/0596008945). Pretty good explanations
of database and query concepts.|||You may also want to pick up some books on Data Warehousing; different
design model, and different design rules, but if you're dealing with
millions of rows of data, you may find yourself looking at OLAP cubes
eventually. Look for stuff by Kimball.
I had to laugh recently: Microsoft is running this ad campaign about
companies that run SQL Server 2005; Hilton supposedly has a 7 million
row data warehouse that load 1.4 million rows of data a day. In our
shop, we process over 40 million rows of data a day on SQL 2000.
SQL Server 2005 is very nice, and there are a lot of performance
enhancements, but SQL 2000 has been an enterprise class database for a
long time. If you have the choice, go with SQL 2005; if not, you can
do a lot with SQL 2000
Stu
Jarod wrote:
> Hey
> I need some good books about working with enormous amount of data. The tab
le
> rows are counted in milions. Have you used SS-2005 with such big amount of
> data ? What was your experiences ? Any bad things happend ? Any supprises
?
> Any bugs ? Is there big perfomance diffrence between SS-2000 and 2005 ?
> What books will you recommend to read to get to know some nice techniques
?
> What articles ? What websites ? Maybe there is some forum for developers /
> programmers for such databases ?
> Please share your experience :)
> Jarod