Hi everyone,
my task was to show 2 lines in a line chart where one line is for actual and other is for plan costs per Periode. Now the customer is asking to create another graph where the same comparison happens using cumulative values.
My table of costs has the following structure where each row is a function/person that is booking costs in a certain periode. Per each Periode (P01-12) there is an actual and a plan value per function.
| Function | P01 Actual | P01 Plan | P02 Actual | P02 Plan |
| Bid Director | 500 € | 300 € | 400 € | 400 € |
| QA | 200 | 200 | 350 | 300 |
For, the indivual values, I had created a new table where I categorized the costs based on actual and plan cost.
UNION(
SELECTCOLUMNS(
'Costs',
"Periode", "P01",
"Cost Type", "Actual Cost",
"Cost", 'Costs'[P01 Actual], 'Cost'[Function]
),
SELECTCOLUMNS(
'Costs',
"Periode", "P01",
"Cost Type", "Plan Cost",
"Cost", 'Costs'[P01 Plan], 'Cost'[Function]
),
So the result table structure was something like this:
| Periode | Cost Type | Cost | Function |
| P01 | Actual Cost | 500 € | Bid Director |
| P01 | Plan Cost | 300 € | Bid Drector |
I am aiming to create a new table with cumulative values, but I haven't had any success so far. One challenge I faced with the individual cost was sorting the table starting at Periode 11 continuing at Periode 12, 01, etc. I managed to solve this by creating an additional table with the sorting logic and establishing a relationship to the result table adding the sorting order from that additional table. So again, I would aim for a similar solution. However, I am struggling with the cumulative sum, as it needs to start at P11, but it somehow starts at P01 or in other results do not cumulate at all but instead just takes the indivual value.
