how to get total automatically for each 5 rows.

[learner_002]

Hello @ALL,

I am new to Power Bi, I need some help here, I want to create a automatical total for each 5 rows but the next 5 rows total cannot add on 1st 5 rows and at final i need complete total. can anyone know how to do it. Thanks in advance.

slno	Transation	Debit
1	Transation1	100
2	Transation2	100
3	Transation3	100
4	Transation4	100
5	Transation5	100
6	Transation6	100
7	Transation7	100
8	Transation8	100
9	Transation9	100
10	Transation10	100
11	Transation11	100
12	Transation12	100
13	Transation13	100
14	Transation14	100
15	Transation15	100
16	Transation16	100
17	Transation17	100
18	Transation18	100
19	Transation19	100
20	Transation20	100
21	Transation21	100

[Fowmy · Accepted solution]

@learner_002 

Add the following code as a new column to your table:

Total = 
IF( 
    MOD(Table2[slno],5) = 0 ,
    CALCULATE(
        SUM(Table2[Debit]),
        FILTER(
            Table2,
            Table2[slno] <= EARLIER(Table2[slno]) && Table2[slno] >= EARLIER(Table2[slno]) - 4
        )
    )
) 

If you need a measure to perform the same, try the following measure:

Total Measure = 
IF( 
    MOD(MAX(Table2[slno]),5) = 0 ,
    CALCULATE(
        SUM(Table2[Debit]),
        FILTER(
            ALL(Table2[slno]),
            Table2[slno] <= max(Table2[slno]) && Table2[slno] >= max(Table2[slno]) - 4
        ),
        ALL(Table2)
    )
) 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[AlB]

Hi @learner_002 

Show the expected result for your sample data to help clarify what you need.

And explain whether you need this in a calculated column on the same table or if not how...

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers