Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.