Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cumulative total in stacked bar graph

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 ...
Need Help
Tutorial Requests
  • Icey's avatar
    Icey
    6 years ago

    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's avatar
rruthSL
Icon for Advocate I rankAdvocate I
10 months ago

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.] )
)