Building a cumulative subtotal

[rodrigorsrv]

Hi People!

I'm trying to build an income statement report and I'm at a loss with building something so apparently so simple as the cumulative subtotals.

My data table looks like this

Account | Category | Amount | Year

And I'd like to get to this:

Category	Amount
Revenues	100
Costs	(40)
Gross profit	60
Administrative expenses	(11)
Other operating results	4
Operating profit	53
Financial results	(12)
Net income before tax	41
Income tax	(10)
Net profit	31

So basically up until Gross profit I'm ok but how could I make it that operating profit not only adds Administrative expenses and other operating results but also the previous subtotal of Gross profit (same with the following subtotals)?

I though about making it through some power query grouping, table referencing and appending but I don't think it would be a proper way of doing it so.

Any help will be appreciated!

[Sahir_Maharaj]

Hello @rodrigorsrv,

Can you please try?

1. Create a Total Amount Measure

Total Amount = SUM('Table'[Amount])

2. Create a Cumulative Subtotal Measure

Cumulative Subtotal = 
CALCULATE(
    [Total Amount],
    FILTER(
        ALL('Table'),
        'Table'[CategoryOrder] <= MAX('Table'[CategoryOrder])
    )
)

[rodrigorsrv]

Thanks!

I tried as you said but I arrived to this:

Maybe I didn't fully understand but I didn't get exactly what I wanted. These running subtotales should be in the same column.

Alternatively I tried this but I think it's overly complex and not scalable:

1. Modified the data structure

2.  Next, I created only one conditional measure:

Total Amount = if(AVERAGE('Table'[Level])<2,sum('Table'[Amount]),CALCULATE(
    sum('Table'[Amount]),
    FILTER(
        ALL('Table'),
        'Table'[CategoryOrder] <= MAX('Table'[CategoryOrder])
    )
))

3. I've ended up with this, as you may see it wasn't really a practical solution.