cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
TIMBOP
New Member

PowerBI - calculate cumulative values with multiple criteria

Hello PowerBI Community.

 

I've been battling with something and would appreciate some assistance.  I have a table with a set of budget and actual spend figures per project per month.  These have been brought into PowerBI. I now want to calculate the cumulative / YTD figures for each project.

 

In the table below I have a simplified example:

  • the Projects are: Blue, Red
  • the Types are: Budget, Actual
  • the Months are: Jan, Feb, Mar and April 2022
  • the table name is [MyTable]

Project | Type | Month | Amount || What I want!
Blue | Budget | Jan 22 | $50 || $50
Blue | Budget | Feb 22 | $50 || $100
Blue | Budget | Mar 22 | $50 || $150
Blue | Budget | Apr 22 | $50 || $200
Blue | Actual | Jan 22 | $40 || $40
Blue | Actual | Feb 22 | $65 || $105
Blue | Actual | Mar 22 | $35 || $140
Blue | Actual | Apr 22 | $40 || $180
Red | Budget | Jan 22 | $30 || $30
Red | Budget | Feb 22 | $30 || $60
Red | Budget | Mar 22 | $30 || $90
Red | Budget | Apr 22 | $30 || $120
Red | Actual | Jan 22 | $20 || $20
Red | Actual | Feb 22 | $25 || $45
Red | Actual | Mar 22 | $45 || $90
Red | Actual | Apr 22 | $32 || $122

 

I can add a calculated column using the following:

Cumulative = CALCULATE(SUM(MyTable[Amount]0, All(MyTable), MyTable[Month] <= EARLIER(MyTable[Month]))

 

This creates the column, but sums the value for both / all projects.

 

How do I get the calculation to filter on the project AND type?  The last column above shows what I am trying to achieve.

1 ACCEPTED SOLUTION

@TIMBOP 

Please use

Cumulative =
VAR CuttentMonth = MyTable[Month]
RETURN
    CALCULATE (
        SUM ( MyTable[Amount] ),
        ALLEXCEPT ( MyTable, MyTable[Project], MyTable[Type] ),
        MyTable[Month] <= CuttentMonth
    )

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

Hi @TIMBOP 

please use

Cumulative =
VAR CuttentMonth = MyTable[Month]
RETURN
    CALCULATE (
        SUM ( MyTable[Amount] ),
        ALLEXCEPT ( MyTable, MyTable[Project] ),
        MyTable[Month] <= CuttentMonth
    )

Hello @tamerj1 

 

Thank you.

 

I've tried this.  I get cumulative values calculated, split by project but not (yet) by type.  Does something need to be added to achieve this?

@TIMBOP 

Please use

Cumulative =
VAR CuttentMonth = MyTable[Month]
RETURN
    CALCULATE (
        SUM ( MyTable[Amount] ),
        ALLEXCEPT ( MyTable, MyTable[Project], MyTable[Type] ),
        MyTable[Month] <= CuttentMonth
    )

THANK YOU SO MUCH.  That works a treat! Much appreciated.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors