how to calculate sum of a column excluding the last row

[mohiniverma2704]

Hi All, 

I have a table, where the need the total of the Region column but need to exclude "Grand total" value from that sum. So just sum of anything before Grand Total.

[Thennarasu_R · Accepted solution]

Hi @mohiniverma2704 

Good Day !
First go to create the rank desc order based on Index column after that you should remoe the rank 1 . rank 1 will apper in last row .

Try this calculation for your requitrements

Measure =Var _AA=

RANKX(ALL(Table name ),CALCULATE(SUM(Index column),,Desc)
Return
Calculate(sum(Output Column),__AA<>1)

Thanks,
Thennarasu R

[v-tangjie-msft · Accepted solution]

Hi @mohiniverma2704 ,

Thanks to @Thennarasu_R  and @netanel  for their quick replies. Please allow me to offer an alternative idea:

(1) This is my test data.

(2) We can create a measure.

MEASURE =
IF (
    ISFILTERED ( 'Table'[Region] ),
    SUM ( 'Table'[Value] ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Region] <> "Grand total" )
    )
)

Or if you are limited to excluding the last row, then we need to click on "transform data" to go to the power query and add an index column.

Then we can create a measure.

Measure 2 = 
var _max_row=MAXX(ALL('Table'),[Index])
RETURN IF(ISFILTERED('Table'[Region]),SUM('Table'[Value]),CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),[Index] <> _max_row)))

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Thennarasu_R · Accepted solution]

Hi @mohiniverma2704 

Good Day !
First go to create the rank desc order based on Index column after that you should remoe the rank 1 . rank 1 will apper in last row .

Try this calculation for your requitrements

Measure =Var _AA=

RANKX(ALL(Table name ),CALCULATE(SUM(Index column),,Desc)
Return
Calculate(sum(Output Column),__AA<>1)

Thanks,
Thennarasu R

[netanel]

Hi @mohiniverma2704 

This one should work

Sum_Without_Grand_Total =
IF(
ISINSCOPE('Yure_Table'[SomeColumn]),
SUM('Yure_Table'[Amount]),
BLANK()
)