Cummulative sum of a field without having a reference field such as a date

[BorislavK]

Hello, everybody!

I am new to Power BI and I am trying to do the following thing. Let's say that I have the following type of table, which represents a set of projects each having a name, cost, NPV and NPS impact:

I want to be able to fixate a number (let's say 450) for a budget amount and then, according to the order of the projects, colour all the rows of projects that fall out of the budget. We assume that after the rows have been sorted in a particular way, the projects will be carried out in the order that they come. Therefore, in this case (on the picture), the budget will be exhausted by the first three projects and the fourth project is coloured in red.

My plan to carry this out was to create a measure that is a rolling/cumulative sum of the project costs (budget) up to and including the current row (let's call it CummulativeBudget). If the value of that measure for the current row exceeds the fixated number (e.g. 450), then I would change the colour of the row through conditional formatting.

However, I faced many difficulties with creating this column and I am stuck for a few hours now.

I tried implementing it for the NPV column only, in ascending order. My plan was to create another measure (let's call it NPVAscendingRank), which, after sorting the projects on NPV in ascending order, would serve as an index of the current row. I did not succeed with this. Here is the formula that I used:

NPVAscendingRank = RANKX(ALL(ProjectsTable),ProjectsTable[NPV],MAX(ProjectsTable[NPV]), ASC, Dense)

And here is the result that I got:

So, the problem is that the rows which have the same value for NPV will have the same rank and therefore I cannot implement the cumulative sum (it will sum the budget requirements of all projects with this NPV, which is not what I am aiming for).

I would be very grateful if somebody could help me out with fixing this rank measure. I am also open to alternatives in any other part of the assignment.

[Anonymous]

Hi @BorislavK ,

Can we use Power Query Editor?

In Power Query Editor, sort the table by [NPV] and then add an index column.

Then back to Power BI you could use index column to calculate the cumulative sum.

Best Regards,

Jay

[daniel79]

Hi Borislav,

Here's my take on this, hopefully it will be useful to you.

I created two measues NPVRank sorts by NPV and Name to get each project and individual rank number.

NPVRank = 
    VAR vProject = MAX(Projects[Name])
    VAR vNPV = CALCULATE(maxx(Projects,Projects[NPV]),Projects[Name] = vProject)

    VAR vCount = COUNTROWS(filter(all(Projects),Projects[NPV]<vNPV || (Projects[NPV]=vNPV && Projects[Name]<vProject)))+1
    RETURN vCount

Overbudget then just calculates projects that go above the budget parameter threshold.

OverBudget? = 
    VAR vProject = MAX(Projects[Name])
    VAR vNPV = CALCULATE(maxx(Projects,Projects[NPV]),Projects[Name] = vProject)

    VAR vSum = sumx(filter(all(Projects),Projects[NPV]<vNPV || (Projects[NPV]=vNPV && Projects[Name]<vProject)),sum(Projects[Cost]))
    return if(vSum > Budget[Budget Value], 1,0)

You can find the pbix and data file here.