October 28, 2013 at 7:58 am
Hey all I'm hoping you can help me with an MDX problem I am having. I will use adventure works for this example. Basically I have been asked to create a calculated measure in the cube that will display a cumulative or rolling sum of another measure. I have found quite few solutions but all of them use a specific dimension such as date with YTD in the SUM but I was hoping to create a measure that was not dependent on any specific dimension.
The simplest MDX I could find is in the example below. While this works in MDX on the fly, I can't use it in Excel in any other dimension besides the product line. I was hoping there was a way to make it dynamic so that it would provide the cumulative sum no matter what dimensions are dropped on.
I was thinking that i would have to somehow use the Descendants function with a specified Axis but my knowledge of MDX is so limited that I am probably way off. Any ideas?
with member [Measures].[Running total] as
Sum(null : [Product].[Product Line].CurrentMember, [Measures].[Internet Sales Amount]),
Format = "Currency"
select {[Measures].[Internet Sales Amount],[Measures].[Running total]} on 0,
[Product].[Product Line].[Product Line].Members on 1
from [Adventure Works]
October 29, 2013 at 4:12 am
Something like this would work:
[Code="Other"]
WITH MEMBER [C]
AS
SUM(NULL:Axis(1).Item(0).Item(Axis(1).Item(0).Count-1).Hierarchy.CurrentMember, [Measures].[Your Measure])
SELECT
{[Measures].[Your Measure], [c]} ON 0,
[Your Dimension].[Your Hierarchy or Attribute] ON 1
FROM
[Your Cube]
[/code]
Hope that helps 🙂
November 17, 2015 at 9:51 am
Please,
Help me.
With a dimension works.
If you use two attributes of the same dimension:
Year;
Month.
Does not work.
Example 01:
WITH
MEMBER [Measures].[SALDO_INICIAL] AS SUM(NULL:Axis(1).Item(0).Item(Axis(1).Item(0).Count-1).Hierarchy.PREVMEMBER, [Measures].[QTDE_TOTAL_ENTRADAS] - [Measures].[QTDE_TOTAL_SAIDAS])
MEMBER [Measures].[SALDO_FINAL] AS SUM(NULL:Axis(1).Item(0).Item(Axis(1).Item(0).Count-1).Hierarchy.CurrentMember, [Measures].[QTDE_TOTAL_ENTRADAS] - [Measures].[QTDE_TOTAL_SAIDAS])
SELECT
{
[Measures].[SALDO_INICIAL],
[Measures].[QTDE_TOTAL_ENTRADAS],
[Measures].[QTDE_TOTAL_SAIDAS],
[Measures].[SALDO_FINAL]
} ON 0,
[DATAS].[MES].[MES] ON 1
FROM
[Model]
Attachment: img1.jpg
Example 02:
WITH
MEMBER [Measures].[SALDO_INICIAL] AS SUM(NULL:Axis(1).Item(0).Item(Axis(1).Item(0).Count-1).Hierarchy.PREVMEMBER, [Measures].[QTDE_TOTAL_ENTRADAS] - [Measures].[QTDE_TOTAL_SAIDAS])
MEMBER [Measures].[SALDO_FINAL] AS SUM(NULL:Axis(1).Item(0).Item(Axis(1).Item(0).Count-1).Hierarchy.CurrentMember, [Measures].[QTDE_TOTAL_ENTRADAS] - [Measures].[QTDE_TOTAL_SAIDAS])
SELECT
{
[Measures].[SALDO_INICIAL],
[Measures].[QTDE_TOTAL_ENTRADAS],
[Measures].[QTDE_TOTAL_SAIDAS],
[Measures].[SALDO_FINAL]
} ON 0,
(
[DATAS].[ANO].[ANO],
[DATAS].[MES].[MES]
) ON 1
FROM
[Model]
Attachment: img2.jpg
June 25, 2018 at 9:50 am
PB_BI - Tuesday, October 29, 2013 4:12 AMSomething like this would work:[Code="Other"]WITH MEMBER [C] ASSUM(NULL:Axis(1).Item(0).Item(Axis(1).Item(0).Count-1).Hierarchy.CurrentMember, [Measures].[Your Measure])SELECT{[Measures].[Your Measure], [c]} ON 0,[Your Dimension].[Your Hierarchy or Attribute] ON 1FROM[Your Cube][/code]Hope that helps 🙂
This solution was very helpful for me, but hit in a second obstacle, when realize a filter in the dimension of fear the accumulated value changes, needs to the filter not interfere to that extent. Can help me?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply