Forum Discussion

rodrigorsrv's avatar
rodrigorsrv
New Member
2 years ago

Building a cumulative subtotal

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:

 

CategoryAmount
Revenues100
Costs(40)
Gross profit60
Administrative expenses(11)
Other operating results4
Operating profit53
Financial results(12)
Net income before tax41
Income tax(10)
Net profit31

 

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!

 

 

Tips and Tricks

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      rodrigorsrv
      New Member
      2 years ago

      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.