Have a look at this query
WITH
MEMBER [Measures].[REFUNDS] AS [Measures].[Internet Sales Amount] – [Measures].[Internet Sales Amount] * .4
MEMBER [Measures].[PROFIT] AS [Measures].[Internet Sales Amount] – [Measures].[REFUNDS]
MEMBER [Product].[Product].[Top10] AS
sum(
TOPCOUNT([Product].[Product].[Product].MEMBERS
, 10
, [Measures].[Internet Sales Amount]
)
,
([Measures].CurrentMember)
)
MEMBER [Product].[Product].[Top10PercOfTotal] AS [Product].[Product].[Top10]/[Product].[Product].[All Products]
SELECT
{
[Measures].[Internet Sales Amount],
[Measures].[REFUNDS],
[Measures].[PROFIT]
} ON COLUMNS,
{
[Product].[Product].[Top10]
,[Product].[Product].[Top10PercOfTotal]
,[Product].[Product].[All Products]
}
ON ROWS
FROM [Adventure Works]
This yeilds same result as yours ie. not a flat cell by cell division
|
Internet Sales Amount |
REFUNDS |
PROFIT |
|
|
Top10 |
$10,355,525.92 |
$6,213,315.55 |
$4,142,210.37 |
|
Top10PercOfTotal |
0.35272454 |
0.211634724 |
0.141089816 |
|
All Products |
$29,358,677.22 |
$17,615,206.33 |
$11,743,470.89 |
I don’t expect it to produce this result . If the computations was going as planned the output should be 0.35272454 (35 %) for each.
I have altered the SOLVE ORDER (this is the property which determines the order of cell computation)
Here is modified query
WITH
MEMBER [Measures].[REFUNDS] AS [Measures].[Internet Sales Amount] - [Measures].[Internet Sales Amount] * .4,SOLVE_ORDER = 1
MEMBER [Measures].[PROFIT] AS [Measures].[Internet Sales Amount] - [Measures].[REFUNDS],SOLVE_ORDER = 2
MEMBER [Product].[Product].[Top10] AS
sum(
TOPCOUNT([Product].[Product].[Product].MEMBERS
, 10
, [Measures].[Internet Sales Amount]
)
,
([Measures].CurrentMember)
)
,SOLVE_ORDER = 3
MEMBER [Product].[Product].[Top10PercOfTotal] AS
[Product].[Product].[Top10]/[Product].[Product].[All Products]
, FORMAT_STRING = "Percent"
, SOLVE_ORDER = 4
SELECT
{
[Measures].[Internet Sales Amount],
[Measures].[REFUNDS],
[Measures].[PROFIT]
} ON COLUMNS,
{
[Product].[Product].[Top10]
,[Product].[Product].[Top10PercOfTotal]
,[Product].[Product].[All Products]
}
ON ROWS
FROM [Adventure Works]
·
|
Internet Sales Amount |
REFUNDS |
PROFIT |
|
|
Top10 |
$10,355,525.92 |
6213315.552 |
4142210.368 |
|
Top10PercOfTotal |
35.27% |
35.27% |
35.27% |
|
All Products |
$29,358,677.22 |
$17,615,206.33 |
$11,743,470.89 |
There you go. SOLVE_ORDER determines the series of how cell computation will be formed. One with smallest SOLVE_ORDER will be evaluated first.
Hope this explains SOLVE_ORDER and how it works.
