Cumulative total in stacked bar graph

[Anonymous]

Hi everyone, I am quite new to Power BI.

I'm trying to present a graph that adds the cumulative total of a column over each month. The data set is from April 2020 to March 2021. 

I have used the following Measure to calculate the running total: 

Running Total MEASURE = CALCULATE (SUM ( 'Data'[Savings] ), FILTER (ALLSELECTED( 'Data' ), Data[Date] <= MAX(Data[Date])))

This measure works well for a basic bar graph:

However, I would like to split this by category in a stacked bar graph. Whenever I try and add a data field in the legend, it doesn't seem to be splitting up the allocations correctly:

This second graph should ideally show the same total values as the first one, only split by status, ideally looking something like this:

Any help would be appreciated, thank you!

[rruthSL]

This solution works. Though if you have a calendar table, be sure to mind the differences below. Also, note the difference in the CALCULATE function's first operand - This is the solution to overcounting which you may or may not encounter.

Running Total MEASURE =

CALCULATE (

DISTINCTCOUNT ( 'Data'[ID] ),

FILTER (

/* 1. Keep all days in the Calendar table, then
2. Keep only those up to the last visible day */

ALL ( 'Calendar' ), 

'Calendar'[Date] <= MAX ( 'Calendar'[Date] )

),

ALLEXCEPT ( 'Data', 'Data'[Line No.] )

)

[Icey · Accepted solution]

Hi @Anonymous ,

Here is my sample data.

1. Create three calculated columns to sort the months of the chart by year.

Month = MONTH([Date])

Year = YEAR([Date])

Year-Month = [Year]&"-"&[Month]

2. You can write your measure as below. I changed ALLSELECTED to ALLEXCEPT.

Running Total MEASURE =
CALCULATE (
    SUM ( 'Data'[Savings] ),
    FILTER (
        ALLEXCEPT ( Data, Data[Categories] ),
        Data[Date] <= MAX ( Data[Date] )
    )
)

You can check more details from here.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rruthSL]

This solution works. Though if you have a calendar table, be sure to mind the differences below. Also, note the difference in the CALCULATE function's first operand - This is the solution to overcounting which you may or may not encounter.

Running Total MEASURE =

CALCULATE (

DISTINCTCOUNT ( 'Data'[ID] ),

FILTER (

/* 1. Keep all days in the Calendar table, then
2. Keep only those up to the last visible day */

ALL ( 'Calendar' ), 

'Calendar'[Date] <= MAX ( 'Calendar'[Date] )

),

ALLEXCEPT ( 'Data', 'Data'[Line No.] )

)

[Fowmy]

@Icey 

That was smart !

Another alternative will be 

Running Total MEASURE = 
CALCULATE (
    SUM ( 'Data'[Savings] ),
    FILTER (
        ALL(Data[Date],Data[Year-Month]),
        Data[Date] <= MAX ( Data[Date] )
    )
)

Thanks
Fowmy

[Fowmy]

@Anonymous 

Each category will have its own data and will not allocate into anything. insert a slicer for the category, select each and check it works when the graph has only one. It stacks one on top of the other. Change the graph to line or area chart to what you are expecting.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[amitchandak]

@Anonymous , Create a date table and join date with date and try like

Running Total MEASURE = CALCULATE (SUM ( 'Data'[Savings] ), FILTER (ALLSELECTED( 'Date' ), Date[Date] <= MAX(Date[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.