Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 centre | Owner | Period 1 | Period 2 | Period 3 | Period 4 | Period 5 | Period 6 | Period 7 | Period 8 | Period 9 | Period 10 |
cost centre 1 | John | 11 | 33 | 7 | 9 | 34 | 56 | 9 | 0 | 23 | 1 |
cost centre 2 | Tom | 2 | 1 | 67 | 89 | 43 | 22 | 6 | 78 | 9 | 2 |
cost centre 3 | Tom | 98 | 7 | 65 | 40 | 12 | 31 | 7 | 0 | 3 | 4 |
cost centre 4 | John | 9 | 7 | 4 | 3 | 2 | 8 | 9 | 9 | 1 | 1 |
cost centre 5 | Tom | 6 | 17 | 4 | 5 | 18 | 30 | 5 | 0 | 12 | 1 |
cost centre 6 | John | 9 | 1 | 6 | 4 | 1 | 3 | 1 | 0 | 0 | 0 |
cost centre 7 | Tom | 20 | 12 | 17 | 15 | 12 | 14 | 12 | 11 | 11 | 11 |
cost centre 8 | John | 100 | 7 | 0 | 3 | 7 | 65 | 40 | 7 | 65 | 40 |
cost centre 9 | Tom | 11 | 33 | 7 | 9 | 34 | 56 | 9 | 0 | 23 | 1 |
cost centre 10 | Tom | 2 | 1 | 67 | 89 | 43 | 22 | 6 | 78 | 9 | 2 |
cost centre 11 | Tom | 6 | 17 | 4 | 5 | 18 | 30 | 5 | 0 | 12 | 1 |
cost centre 12 | Tom | 11 | 33 | 7 | 9 | 34 | 56 | 9 | 0 | 23 | 1 |
I'd be grateful for your help.
E.
Solved! Go to Solution.
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:
Then add a PeriodNumber column for calculation.
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] ) ) )
Regards,
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:
Then add a PeriodNumber column for calculation.
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] ) ) )
Regards,
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |