Stacked Column chart with cumulative sum over year by Category

[sarabaia]

Hello, 

I am trying to create a stacked column chart with cumulative sum.
I used the following formula to calculate the cumulative sum:

CumulativeCapacity = CALCULATE(SUM(Append1[MWel]),

                        FILTER(ALL(Append1),Append1[Announced start date]<=MAX(Append1[Announced start date])))

That works when I use without any field in category:

But when I try to add category, it multiplies each sum per category for the number of categories that year:

I then change the DAX formula to:

CumulativeCapacity = CALCULATE(SUM(Append1[MWel]),

                        FILTER(ALLEXCEPT(Append1, Append1[Current stage]),Append1[Announced start date]<=MAX(Append1[Announced start date])))

Now the cumulative sum is right but it does not show me categories from previous years, see bellow:

My problem is that I dont have all Categories = "Current Stage" for all years. But I would like to see per instance in 2032 also the cumulative sum for construction, feasibility study and preparatory stage. How can I solve this issue?

[v-shex-msft]

Hi @sarabaia,

You cna try to use the follwoing meaure formula to the get cumlative result based current date and stage groups:

CumulativeCapacity =
CALCULATE (
    SUM ( Append1[MWel] ),
    FILTER (
        ALLSELECTED ( Append1 ),
        Append1[Announced start date] <= MAX ( Append1[Announced start date] )
    ),
    VALUES ( Append1[Current stage] )
)

Regards,
Xiaoxin Sheng

[sarabaia]

Sorry but does not work, when I use the proposed formula this is my result and I want to see as well 2032-2040 the sum of other categories even if there was no addition on that year in that categories/stage:

[v-shex-msft]

Hi @sarabaia ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[sarabaia]

Hello please see attached, the pbix file with dummy data: https://we.tl/t-oxXJb0Wnvr . And the results with proposed formulas. 

Anyway I solve the issue creating a calculated table:

CalculatedTable =

ADDCOLUMNS(

    CROSSJOIN(

        DISTINCT('Table'[Announced Start Year]),

        DISTINCT('Table'[Stage])

    ),

    "Capacity", CALCULATE(SUM('Table'[Capacity]))

)
And then using this measure to the cumulative sum:

CumulativeSum =

VAR CurrentYear = MAX(CalculatedTable[Announced Start Year])

RETURN

CALCULATE(

    SUM(CalculatedTable[Capacity]),

    FILTER(

        ALLEXCEPT('CalculatedTable',CalculatedTable[Stage]),

        'CalculatedTable'[Announced Start Year] <= CurrentYear

    ))


Used the fields of the calculated table and cumulative sum measure and it worked out (results in graph 3 of the attached file). However I am still interested to know if there is any solution without creating a new table.