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

Grabbing a calculated field for a new calculation

I'm going to try and explain this as best as I can :)
I have calculated fields that has the following expressions - I've
named each textbox hoping that I could grab them in the expression but
I have no idea how':
1. totalMarket: =iif(sum(Fields!Wins.Value) = 0, 0, sum( Fields!
Wins.Value) + iif(sum(Fields!Pending.Value) = 0, 0, sum(Fields!
Pending.Value))) - iif(sum(Fields!Losses.Value) = 0, 0, sum(Fields!
Losses.Value))
2. remainingSales = =iif(Sum(Fields!Pending.Value)=0, 0, Sum(Fields!
Pending.Value))
I need the next text box (marketPercent) to do the following...
totalMarket - remainingSales / totalMarket
I used all the expression above to make it work - but there has to be
an easier way.. I tried using..
=iif(Sum(ReportItem!totalMarket.Value)) ... etc but that syntax did
not work... does anyone know how to grab the name of the textbox in
an expression?
ps - this is not a header or a footer - it's in the actual form
Thanks in advance!
LisaOn Jun 6, 10:38 am, peashoe <peas...@.yahoo.com> wrote:
> I'm going to try and explain this as best as I can :)
> I have calculated fields that has the following expressions - I've
> named each textbox hoping that I could grab them in the expression but
> I have no idea how':
> 1. totalMarket: =iif(sum(Fields!Wins.Value) = 0, 0, sum( Fields!
> Wins.Value) + iif(sum(Fields!Pending.Value) = 0, 0, sum(Fields!
> Pending.Value))) - iif(sum(Fields!Losses.Value) = 0, 0, sum(Fields!
> Losses.Value))
> 2. remainingSales = =iif(Sum(Fields!Pending.Value)=0, 0, Sum(Fields!
> Pending.Value))
> I need the next text box (marketPercent) to do the following...
> totalMarket - remainingSales / totalMarket
> I used all the expression above to make it work - but there has to be
> an easier way.. I tried using..
> =iif(Sum(ReportItem!totalMarket.Value)) ... etc but that syntax did
> not work... does anyone know how to grab the name of the textbox in
> an expression?
> ps - this is not a header or a footer - it's in the actual form
> Thanks in advance!
> Lisa
As far as I know, referencing the textbox control by an alias is not
possible. Referencing the textbox by the expression used is most
likely the only way to reference it. Sorry that I could not be of
greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultantsql