Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ewuchatka
Helper II
Helper II

Running total per period

Hello

 

I am quite new to Power BI and I'd be grateful for your help.

 

I'd like to create two charts where I will be able to view a running total for:

1. Chart for each Owner (as a running total of all cost centres altogether by period - so a signle column per period) - you can see that there are two owners and they are assigned different cost centres.

2. Chart for each Owner (as a running total for each cost centre by period) - so for John there will be four columns per period.

 

Please note that the data is organised by Period, not Month.

 

The data is organised in the following way:

 

Cost centreOwnerPeriod 1Period 2Period 3Period 4Period 5Period 6Period 7Period 8Period 9Period 10
cost centre 1John113379345690231
cost centre 2Tom216789432267892
cost centre 3Tom987654012317034
cost centre 4John9743289911
cost centre 5Tom61745183050121
cost centre 6John9164131000
cost centre 7Tom20121715121412111111
cost centre 8John1007037654076540
cost centre 9Tom113379345690231
cost centre 10Tom216789432267892
cost centre 11Tom61745183050121
cost centre 12Tom113379345690231

 

I'd be grateful for your help.

 

E.

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@ewuchatka

 

According to your description, is your running total calculation based on period or cost centers?

 

I assume your running total is based on period and your need to calculate it for all cost centers and within each cost center.

 

Firstly, please unpivot your table like below:

 

34.PNG

 

Then add a PeriodNumber column for calculation.

 

45.PNG

 

56.PNG

 

Then you can create two measures;

 

 

Runnint Total for all cost centres =
CALCULATE (
    SUM ( Table3[Value] ),
    FILTER (
        ALLEXCEPT ( Table3, Table3[Owner] ),
        Table3[PeriodNumber] <= MAX ( Table3[PeriodNumber] )
    )
)
Runnint Total within cost centre =
CALCULATE (
    SUM ( Table3[Value] ),
    FILTER (
        ALLEXCEPT ( Table3, Table3[Owner], Table3[Cost centre] ),
        Table3[PeriodNumber] <= MAX ( Table3[PeriodNumber] )
    )
)

44.PNG

 

566.PNG

 

Regards,

 

View solution in original post

1 REPLY 1
v-sihou-msft
Employee
Employee

@ewuchatka

 

According to your description, is your running total calculation based on period or cost centers?

 

I assume your running total is based on period and your need to calculate it for all cost centers and within each cost center.

 

Firstly, please unpivot your table like below:

 

34.PNG

 

Then add a PeriodNumber column for calculation.

 

45.PNG

 

56.PNG

 

Then you can create two measures;

 

 

Runnint Total for all cost centres =
CALCULATE (
    SUM ( Table3[Value] ),
    FILTER (
        ALLEXCEPT ( Table3, Table3[Owner] ),
        Table3[PeriodNumber] <= MAX ( Table3[PeriodNumber] )
    )
)
Runnint Total within cost centre =
CALCULATE (
    SUM ( Table3[Value] ),
    FILTER (
        ALLEXCEPT ( Table3, Table3[Owner], Table3[Cost centre] ),
        Table3[PeriodNumber] <= MAX ( Table3[PeriodNumber] )
    )
)

44.PNG

 

566.PNG

 

Regards,

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.