Cumulative Total in Stacked Bar Chart not working

[RockandGrohl]

Hi, not sure what I'm doing wrong here as I followed the exact steps from another user with the same issue.

Here's my dataset & what is displaying.

Cumulative MW =

CALCULATE(

    SUM('AR Results'[MW]),

    FILTER(

        ALLEXCEPT('AR Results','AR Results'[Tech Type]),

        'AR Results'[DeliveryYearNum] <= MAX('AR Results'[DeliveryYearNum])

    )

)

That's my measure for cumulative, it looks exactly like other solved examples in this forum.

So mine should be showing the same but it's clearly nothing like a cumulative chart.

[RockandGrohl · Accepted solution]

I just ended up doing the calculations myself in Excel per year. (Sumifs on current year + last results)  

It seemed very unintuitive to do it in Power BI. Sorry. It looks like I would have needed to create a dimensional table as MFelix suggested and go from there.

[v-hashadapu]

Hi @RockandGrohl , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[RockandGrohl · Accepted solution]

I just ended up doing the calculations myself in Excel per year. (Sumifs on current year + last results)  

It seemed very unintuitive to do it in Power BI. Sorry. It looks like I would have needed to create a dimensional table as MFelix suggested and go from there.

[v-hashadapu]

Hi @RockandGrohl , Thanks for the update and the relevant information. If you have any other queries, please don't hesitate to creat a new post here in the community. We are always happy to help.

[MFelix · Accepted solution]

Hi @RockandGrohl ,

Glad you were able to figure a solution but if you allow me just a side note, Power BI does not work in the same manner has excel it's based on relational tables this allows for you to maintain a lower data level keeping only the needed information and to have a more wider range specially when you want to cross different properties of your data without the need of duplicating information.

Don't forget to accept the correct answer so it can help others even if it is your own answer.

[v-hashadapu]

Hi @RockandGrohl , Thank you for reaching out to the Microsoft Community Forum.

Just to make sure I understand your issue correctly, could you clarify one thing about the requirement? Are you simply looking for the cumulative MW to calculate and display correctly for each Tech Type based on the data that exists for each Delivery Year or do you also expect all Tech Types to appear in every year even when there is no data (showing zero values)?

The solution I shared focuses on fixing the cumulative behaviour in the stacked chart. If the requirement is the latter as @MFelix says, then using a disconnected or dimension table like they suggested would be the correct approach.

[v-hashadapu]

Hi @RockandGrohl , Thank you for reaching out to the Microsoft Community Forum.

The issue wasn’t the cumulative logic itself, but the evaluation context created by the stacked column chart. Your measure was calculating cumulatives correctly in isolation, but the X-axis was using a different year column than the one driving the cumulative comparison. In stacked visuals, that mismatch causes each legend segment to evaluate its own year context, which leads to the non-cumulative looking bars.

Align the axis with the same numeric year column used in the measure (DeliveryYearNum), ensure the axis is categorical and sorted ascending. Once the axis context is stable, the cumulative DAX behaves exactly as expected for each Tech Type, without needing disconnected tables or model changes. You can verify this by comparing the table visual (which shows correct cumulative values per tech and year) with the stacked column chart, they now match in all cases.

To help you better understand the implementation, I’ve attached the .pbix file for your reference. Please take a look at it and let me know your observations.

[MFelix]

Hi @v-hashadapu 

Apologies for jumping in but this solution does not answer the question of @RockandGrohl  because the problem is that in certain years there aren't certain categories so when they are missing the values are not showing up in the chart that is why you need to have the disconnected table is not a question about the sorting but about the context of the data that is not present when you use a column from the fact table instead of a dimension table or a disconnected table.

Picking your example I added a new category on 2017 and 2019 and we get a break in the chart:

But please @RockandGrohl  lert us know what is the correct problem you are facing.

[MFelix]

Hi @RockandGrohl ,

The question is that you are doing a cumulative based on the tech so for each tech value you are getting the cumulative, however there are no tech values for specific years so that value is not represented on your calculation.

If you look at Solar PV or Offshore wind you will see that cumultive effect.

To solve this you need to have a disconnected table with the tech typwes then change you calculation to the following:

Cumulative MW = 
CALCULATE(
    SUM('AR Results'[MW]),
    FILTER(
        ALLEXCEPT('AR Results','Ar Results'[Tech Type]),
        'AR Results'[DeliveryYearNum] <= MAX('AR Results'[DeliveryYearNum])
        
    ) , 'Ar Results'[Tech Type] in VALUES(Techtype[Tech Type])
)

Has you can see below the calculations are now matching with cumulative bottom chart is your measure top one is the corrected one on the top chart replace the tech type by the disconnected table values

[danextian]

Are you by chance using a column from a different table in your x-axis? If not, can you please post a sanitized copy of your pbix.