Below is the calculated member

[Estimated Value]=

IIf([Measures].[PRC]=0,null,Sum([Product].[MM#].members-[Product].[MM#].[All],[Measures].[PRC])/[Measures].[MM Count])

SELECT {[Measures].[SO Actual Qty],[Measures].[Estimated Value]} ON  COLUMNS,
{ ([Business Unit].[Business Unit].[Business Unit].ALLMEMBERS )} ON ROWS FROM [SMS_CURRENT_WW]
WHERE {[Date- CGID Local].[CGID-QUARTER].&[2013Q1] }

This Calculated Member gives the result as below

 SO Actual Qty Estimated Value
 (null) (null)
ATM (null) (null)
CCDiv 18491 $15,856.61
CD 2008 $9,156.90
CNG (null) (null)
CNG Div 9448 $20,322.75
CPG Div 26 $14,210.00
CPLG Div (null) (null)
CS (null) (null)
DCG CPU 251 $664,899.00
DCPU Div 9863 $31,271.64
DEG (null) (null)
DHED (null) (null)
DPD Biz (null) (null)
DT 78256 $153,214.08
ECG 3472 $167,510.96
ECG Adm (null) (null)
ECPD (null) (null)
ECPD EMD 15 $1,380.00
EPD (null) (null)
EPSD 52 $23,172.67
ESS (null) (null)
FPD (null) (null)
HQ Div (null) (null)
IAD 1227 $3,757.61
ICBD (null) (null)
ISD Biz (null) (null)
LAD 87417 $65,469.21
LCIA (null) (null)
MBL 283574 $521,188.62
McAD 600 (null)
MCG 9450 $35,109.09
MOCS DIV 27 $540.00
MPG 4152 $10,722.69
MWG (null) (null)
NBD 60 $906.67
NPG (null) (null)
NPP (null) (null)
NSG (null) (null)
NSG Div 19133 $41,750.43
NTD 45 $1,845.00
OPD (null) (null)
OTC Div (null) (null)
PEG Div 3750 $6,583.51
RSD (null) (null)
SPD 4179 $4,570.70
SPG (null) (null)
SRV 97182 $861,521.82
Systems 30 (null)
TCD 411 $11,042.86
UMG 5770 $33,560.00
UPSD 3675 $16,053.29
WiFi 4875 $5,617.33
Unknown (null) (null)

the problem i am faced up is the estimated value is calculated correctly at the business unit level but when u add all the business unit data to get sum it is showing wrond data  i e =161,722.688 but when i add  manually it is 2721233.43

that means we are doing wrond aggregation can any body guide me how can i

Hi

Can you check once, your estimated measure is based on [Product].[MM#].Members then presenting with business units

Can you try this once (assuming to use only Business Units for testing)

WITH MEMBER [Measures].[Estimated Value] AS 

IIf([Measures].[MM Count]=0
    ,null
    ,[Measures].[PRC]/[Measures].[MM Count]
    )

 
SELECT {[Measures].[SO Actual Qty],[Measures].[Estimated Value]} ON  COLUMNS, 
{ ([Business Unit].[Business Unit].[Business Unit].Members )} ON ROWS 
FROM [SMS_CURRENT_WW]
WHERE {[Date- CGID Local].[CGID-QUARTER].&[2013Q1] }

Yes Prav the estimated measue based on the [Product].[MM#].Members

each Product member belongs to any of the business units

for example 99898 this is one of the member and belongs to ICBD .There may be another member it belongs to same business unit or some thing else

Here,

You need to get the final results to the business units so with the existing relation to [PRC] and [MM Count] still we need to get the Estimation values with above query

What are the results with the above query.

SO Actual Qty Estimated Value Estimated Value1
(null) (null) (null)
(null) (null) (null)
18491 $15,856.61 37587.16
2008 $9,156.90 10843.2
(null) (null) (null)
9448 $20,322.75 352152.727272727
26 $14,210.00 58500
(null) (null) (null)
(null) (null) (null)
251 $664,899.00 664899
9863 $31,271.64 177150.225680934
(null) (null) (null)
(null) (null) (null)
(null) (null) (null)
78256 $153,214.08 1210522.5
3472 $167,510.96 574215.384615385
(null) (null) (null)
(null) (null) (null)
15 $1,380.00 1380
(null) (null) (null)
52 $23,172.67 28600
(null) (null) (null)
(null) (null) (null)
(null) (null) (null)
1227 $3,757.61 127082.142857143
(null) (null) (null)
(null) (null) (null)
87417 $65,469.21 1454910.27
(null) (null) (null)
283574 $521,188.62 3271548.46315789
600 (null) (null)
9450 $35,109.09 42954.5454545455
27 $540.00 540
4152 $10,722.69 48931.0967741935
(null) (null) (null)
60 $906.67 940
(null) (null) (null)
(null) (null) (null)
(null) (null) (null)
19133 $41,750.43 605198.745901639
45 $1,845.00 1845
(null) (null) (null)
(null) (null) (null)
3750 $6,583.51 113793.103448276
(null) (null) (null)
4179 $4,570.70 5074.5
(null) (null) (null)
97182 $861,521.82 3264707.8125
30 (null) (null)
411 $11,042.86 282591.857142857
5770 $33,560.00 57700
3675 $16,053.29 23740.5
4875 $5,617.33 10400
(null) (null) (null)

Estimated Value 1 is the results from your query

Hi

Still missing something in data, can you post data for these

WITH MEMBER [Measures].[Estimated Value] AS 
IIf([Measures].[MM Count]=0
           ,null
           ,[Measures].[PRC]/[Measures].[MM Count]
   )
SELECT {[Measures].[SO Actual Qty],[Measures].[Estimated Value],[Measures].[PRC],[Measures].[MM Count]
 } ON  COLUMNS, 

NON EMPTY
{[Business Unit].[Business Unit].[Business Unit].Members} ON ROWS 
FROM [SMS_CURRENT_WW]
WHERE ([Date- CGID Local].[CGID-QUARTER].&[2013Q1] )

 SO Actual Qty Estimated Value PRC MM Count
 (null) (null) (null) 0
ATM (null) (null) (null) 0
CCDiv 98426 57915.9305263158 2200805.36 38
CD 56729 50219.1147540984 3063366 61
CNG (null) (null) (null) 0
CNG Div 162835 16562115.0190114 4355836250 263
CPG Div 27057 3865285.71428571 135285000 35
CPLG Div (null) (null) (null) 0
CS 150929 127709.153846154 8301095 65
DCG CPU 725 498920.833333333 2993525 6
DCPU Div 254656 3245615.68627451 3145001600 969
DEG 26581 894893.666666667 102017878 114
DHED 27 6570 19710 3
DPD Biz 1 7149 7149 1
DT 939610 5821791.06628242 2020161500 347
ECG 139073 2875066.82692308 598013900 208
ECG Adm (null) (null) (null) 0
ECPD (null) (null) (null) 0
ECPD EMD 155 7168.75 57350 8
EPD (null) (null) (null) 0
EPSD 8783 529676.535087719 60383125 114
ESS (null) (null) (null) 0
FPD (null) (null) (null) 0
HQ Div (null) (null) (null) 0
IAD 64115 429341.517857143 192345000 448
ICBD (null) (null) (null) 0
ISD Biz 10 (null) (null) 1
LAD 579151 2017465.77418605 86751028.29 43
LCIA 121152 1009600 6057600 6
This is the data.

MBL 2740542 3601479.65467626 3003634032 834
McAD 10450 (null) (null) 21
MCG 25652 98661.5384615385 1282600 13
MOCS DIV 15729 52430 314580 6
MPG 55117 182502.211480363 60408232 331
MWG (null) (null) (null) 0
NBD 8550 50231.25 401850 8
NPG 196 (null) (null) 4
NPP (null) (null) (null) 0
NSG 3 (null) (null) 1
NSG Div 442673 7190627.41116751 2833107200 394
NTD 6203 130263 260526 2
OPD (null) (null) (null) 0
OTC Div (null) (null) (null) 0
PEG Div 107939 1015210.05405405 863943756 851
RSD (null) (null) (null) 0
SPD 54851 210965.384615385 49365900 234
SPG 215 4300 8600 2
SRV 1258622 16027396.2546816 8558629600 534
Systems 2252 4825.71428571429 33780 7
TCD 2370 4307431.11111111 116300640 27
UMG 112538 401921.428571429 16880700 42
UPSD 82865 178159.75 46321535 260
WiFi 69030 39238.1052631579 3727620 95
Unknown (null) (null) (null) 0

Issue with aggregation of all the business units for the estimated value

MM Count is Calculated member Please find the definition as below

count(crossjoin([Product].[MM#].members-[Product].[MM#].[All],Axis(0).Item(0)),EXCLUDEEMPTY)

Hi

Can you confirm, MM Count is getting right values. What is not clear is

1. MM Count is based on projects

2. Estimated values based on projects

but you need final results based on business units, sounds like gaps to understand.

Just a couple of suggestions. First, try creating the calculated measure as a query scoped measure to test that it works as expected before creating it in your cube calculation script. Also, a few other pointers that probably bother me more than others.

• The WHERE clause takes a single tuple as an argument. Tuples are qualified with parentheses and not braces. I believe the MDX should choke and cough on a statement like this but it's way to loosy-goosy with syntax.
• The set definition, [Product].[MM#].members-[Product].[MM#].[All], (again use braces not parentheses to specify a set) can be much more succinctly written by including the level in the initial argument which then excludes the [All] member (assuming that [Product].[MM#] is an attribute hierarchy and not a user-navigable hierarchy). Therefore, [Product].[MM#].[MM#].Members is exactly the same as [Product].[MM#].Members - [Product].[MM#].[All] and doesn't require an EXCEPT to be evaluated.
• But also, I'm in agreement with Prav. Why are you aggregating over all products to get the sum of [Measures].[PRC]? This expression is exactly equivalent to ([Measures].[PRC], [Products].[MM#].[All]) and all you're accomplishing is your unnecessarily burdening the query processor.

I think the problem is your test case isn't valid. Because of the definition of the measure, the SUM({[Business Unit].[Business Unit].[Business Unit].Members, [Measures].[Estimated Value]) will not be equal to ([Business Unit].[Business Unit].[All], [Measures].[Estimated Value]). See below with a simple table of East and West and the corresponding [Measures].[Estimated Values]. If ([Business Unit].[Business Unit].[All], [Measures].[Estimated Value]) SHOULD BE the sum of it's children, you'll have to create a SCOPE statement for it to be such.

An example of such a statement would be the following:

( [Business Unit].[Business Unit].[All], [Measures].[Estimated Value]) 
	= SUM( [Business Unit].[Business Unit].[All].Children, [Measures].[Estimated Value] );

HTH, Martin

I think so.

Estimated value is based on the Business Units

and MM Count is based on Business unit

Product member for for Business Unit for Ex ccDiv

 its like this

Once again i will give all the calculated member dependent on Estimated Value

IIf([Measures].[PRC]=0,null,Sum([Product].[MM#].members-[Product].[MM#].[All],[Measures].[PRC])/[Measures].[MM Count])

([Measures].[PRC])=IIf(IsError(Extract( Axis(0), Measures ).Count), 0,
(Axis(0).Item(0)*[Measures].[Estimated Unit prc]))

[Measures].[MM Count])=count(crossjoin([Product].[MM#].members-[Product].[MM#].[All],Axis(0).Item(0)),EXCLUDEEMPTY)

Hi

To my understanding of your scenarios/data,

Estimated value is only directly depending on Estimated Unit prc and I don't see any value of doing all the above calculations.

Lets do one step at a time and create a step by step test plan on results

Step 1:

SELECT {[Measures].[SO Actual Qty],[Measures].[Estimated Unit prc]
 } ON  COLUMNS, 

NON EMPTY
{[Business Unit].[Business Unit].[Business Unit].Members} ON ROWS 
FROM [SMS_CURRENT_WW]
WHERE ([Date- CGID Local].[CGID-QUARTER].&[2013Q1] )

Step 2:

WITH MEMBER [Measures].[Estimated Value] AS 
IIf(IsEmpty([Measures].[Estimated Unit prc]) or IsEmpty([Measures].[SO Actual Qty])
           ,null
           ,[Measures].[Estimated Unit prc] * [Measures].[SO Actual Qty]
   )
SELECT {[Measures].[SO Actual Qty],[Measures].[Estimated Unit prc], [Measures].[Estimated Value]
 } ON  COLUMNS, 

NON EMPTY
{[Business Unit].[Business Unit].[Business Unit].Members} ON ROWS 
FROM [SMS_CURRENT_WW]
WHERE ([Date- CGID Local].[CGID-QUARTER].&[2013Q1] )

Hey Prav  here is data for the first one

 SO Actual Qty Estimated Unit prc
CCDiv 18491 22.36
CD 2008 54
CNG Div 9448 2460
CPG Div 26 4500
DCG CPU 251 2649
DCPU Div 9863 4616
DT 78256 990
ECG 3472 4300
ECPD EMD 15 92
EPSD 52 1650
IAD 1227 2900
LAD 87417 149.79
MBL 283574 1096
McAD 600 (null)
MCG 9450 50
MOCS DIV 27 20
MPG 4152 1096
NBD 60 47
NSG Div 19133 3859
NTD 45 41
PEG Div 3750 5280
SPD 4179 34
SRV 97182 4300
Systems 30 (null)
TCD 411 4813
UMG 5770 40
UPSD 3675 323
WiFi 4875 32

This is the data for the second query

 SO Actual Qty Estimated Unit prc Estimated Value
CCDiv 18491 22.36 413458.76
CD 2008 54 108432
CNG Div 9448 2460 23242080
CPG Div 26 4500 117000
DCG CPU 251 2649 664899
DCPU Div 9863 4616 45527608
DT 78256 990 77473440
ECG 3472 4300 14929600
ECPD EMD 15 92 1380
EPSD 52 1650 85800
IAD 1227 2900 3558300
LAD 87417 149.79 13094192.43
MBL 283574 1096 310797104
McAD 600 (null) (null)
MCG 9450 50 472500
MOCS DIV 27 20 540
MPG 4152 1096 4550592
NBD 60 47 2820
NSG Div 19133 3859 73834247
NTD 45 41 1845
PEG Div 3750 5280 19800000
SPD 4179 34 142086
SRV 97182 4300 417882600
Systems 30 (null) (null)
TCD 411 4813 1978143
UMG 5770 40 230800
UPSD 3675 323 1187025
WiFi 4875 32 156000