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 | Cat...
Tips and Tricks
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.