How to calculate cumulative sum of a measure with respect to a category variable?

[Cortana]

Hello there,
So here is my data. And the pbix file for more your convenience. I have posted related problems here and got solutions. However, I am stuck again. 

As you open the pbix file and connect the data, you will see a table like this below-

In the stage field, there are more than 5 stages. I have just filtered them out. Here AVG.StageDays are a calculated measure. What I want is below 

That is cumulative sum from the below. Can anyone help me with that? 

[tamerj1 · Accepted solution]

@Cortana 
Here is the sample file with the solution https://we.tl/t-ry6pc8rdIO

Cumulative Total = 
VAR CurrentStage =
    SELECTEDVALUE ( 'Opportunity History'[Stage Number] )
VAR SelectedStages = 
    CALCULATETABLE ( VALUES ( 'Opportunity History'[Stage Number] ), ALLSELECTED ( 'Opportunity History'[StageName] ) )
VAR PreviousSatges =
    FILTER ( SelectedStages, 'Opportunity History'[Stage Number] <= CurrentStage )
RETURN
    SUMX ( PreviousSatges, CALCULATE ( [AVG.StageDays], ALLSELECTED ( 'Opportunity History'[StageName] ) ) )

[tamerj1 · Accepted solution]

@Cortana 
Here is the sample file with the solution https://we.tl/t-ry6pc8rdIO

Cumulative Total = 
VAR CurrentStage =
    SELECTEDVALUE ( 'Opportunity History'[Stage Number] )
VAR SelectedStages = 
    CALCULATETABLE ( VALUES ( 'Opportunity History'[Stage Number] ), ALLSELECTED ( 'Opportunity History'[StageName] ) )
VAR PreviousSatges =
    FILTER ( SelectedStages, 'Opportunity History'[Stage Number] <= CurrentStage )
RETURN
    SUMX ( PreviousSatges, CALCULATE ( [AVG.StageDays], ALLSELECTED ( 'Opportunity History'[StageName] ) ) )

[Cortana]

Thank you so much @tamerj1 . It worked. Thanks again. This is exactly the same as I needed. 

I have to make a table like that so that I can use it as a custom tooltip in a funnel chat. My funnel chart is made of the Stage name and the Average value. But in the tooltip, it will show the cumulative sum that you helped me to make.

So I made a custom tooltip with the stage name and the cumulative sum and linked that with the funnel using the multi-row card. Here it appeares as the table you made.

But when I hover over the funnel it does not show the cumulative sum. It shows the average value. Is there anything to adjust? Is it doable? Can you help me with that, please? Here is the picture.

When I hover on stage 6, it should show the value 281 which is the cumulative sum. Instead, it shows the average value. 

[NickolajJessen · Accepted solution]

Made some changes to the selectedStages variable and i changed the allseltected to all in the last SUMX line. Does this solve your problem?

[Cortana]

@NickolajJessen Hello there, 

I am facing a problem regarding this solution. If I hover over any stage on the funnel it is showing perfectly what I wanted. 

So, now If I filter this for an Opportunity ID that is currently in stage 4, It is showing- 

Here, Shouldn't the "average days to reach closed Won" be 179+80+45= 304?

Will it be possible to show here only 80+45= 125? 

[Cortana]

Thank you so much @NickolajJessen 

It worked. Exactly as I wanted. 

[tamerj1]

Hi @Cortana 

create a calculated column that has the stage number. Make sure it is numeric not string. The create the comulative measure

VAR CurrentStageNum =
SELECTEDVALUE ( TabeName[Satge Number] )

RETURN

SUMX (

FILTER (

ALLSELECTED (TabeName[Satge Number] ),

TabeName[Satge Number] <= CurrentStageNum ),

[MeasureName]
)

[Cortana]

Hello @tamerj1 
Thanks for your suggestion. But it does not work for me. I get exactly the same output as the average. Here is the picture. 

And here is my code 

[tamerj1]

@Cortana 

Just for testing, please remove the column Stage Name from the table visual and replace it with Stage Number. 

[Cortana]

Great!

Yes, it solved the issue. But it is cumulated downwards. Besides I also need to show the stage name. Any way to do that? 

[tamerj1]

@Cortana 

Please try

=
VAR CurrentStageNum =
    SELECTEDVALUE ( TabeName[Satge Number] )
RETURN
    SUMX (
        CALCULATETABLE (
            VALUES ( TabeName[Satge Number] ),
            REMOVEFILTERS ( TableName ),
            TabeName[Satge Number] <= CurrentStageNum
        ),
        [MeasureName]
    )

[Cortana]

In addition, It starts counting from stage number 1, I want it to start from 2. In the previous DAX code, it was starting from the selected value. but here it is not.

[Cortana]

This does work. But If I remove the stage name, it does not work, and also if I remove the Average measure from the table, it does not work as well. 
Actually, I need only the stage name and the Cumulative average duration. Is it possible at all? 

[tamerj1]

Yes should be possible. Can you share a sample file. I'll look into it tomorrow morning

[Cortana]

Many Thanks. It would be very helpful for me. 
I have shared the pbix file and the data in the post. Can you please download it from there? 

[tamerj1]

@Cortana 

Sure. Thank you