Forum Discussion
Show line chart with cumulative values
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.
| Periode | Cost Type | Cost | Function |
| P01 | Actual Cost | 500 € | Bid Director |
| P01 | Plan Cost | 300 € | Bid Drector |
may want to check your math on the Requirements manager.
6 Replies
Thank you. I tried to upload a sample excel file where expected graphs are also included but this is not supported. So anyway in following the sample data:
Cost table
Function Total Actual Cost Total Plan Cost P11 - Actual Cost P11 - Plan Cost P12 - Actual Cost P12 - Plan Cost P01 - Actual Cost P01 - Plan Cost P02 - Actual Cost P02 - Plan Cost P03 - Actual Cost P03 - Plan Cost Bid Director 1.200 € 1.600 € 500 € 500 € 400 € 200 € 300 € 200 € 300 € 400 € Requirements Manager 2.400 € 2.100 € 700 € 300 € 800 € 500 € 900 € 700 € 0 € 600 € Technical Expert 900 € 2.700 € 400 € 600 € 500 € 800 € 0 € 400 € 500 € 400 € After applying code on cost table:
Periode Cost Type Cost Function Sort Order P11 Actual Cost 500 € Bid Director 1 P11 Plan Cost 500 € Bid Director 1 P11 Actual Cost 700 € Requirements Manager 1 P11 Plan Cost 300 € Requirements Manager 1 P11 Actual Cost 400 € Technical Expert 1 P11 Plan Cost 600 € Technical Expert 1 P12 Actual Cost 400 € Bid Director 2 P12 Plan Cost 200 € Bid Director 2 P12 Actual Cost 800 € Requirements Manager 2 P12 Plan Cost 500 € Requirements Manager 2 P12 Actual Cost 500 € Technical Expert 2 P12 Plan Cost 800 € Technical Expert 2 P01 Actual Cost 300 € Bid Director 3 P01 Plan Cost 200 € Bid Director 3 P01 Actual Cost 900 € Requirements Manager 3 P01 Plan Cost 700 € Requirements Manager 3 P01 Actual Cost 0 € Technical Expert 3 P01 Plan Cost 400 € Technical Expert 3 P02 Actual Cost Bid Director 4 P02 Plan Cost 300 € Bid Director 4 P02 Actual Cost Requirements Manager 4 P02 Plan Cost 0 € Requirements Manager 4 P02 Actual Cost Technical Expert 4 P02 Plan Cost 500 € Technical Expert 4 P03 Actual Cost Bid Director 5 P03 Plan Cost 400 € Bid Director 5 P03 Actual Cost Requirements Manager 5 P03 Plan Cost 600 € Requirements Manager 5 P03 Actual Cost Technical Expert 5 P03 Plan Cost 400 € Technical Expert 5 This is the expected table with cumulative values
Expected Table with cumulative values
Periode Cost Type Cost Function Sort Order P11 Actual Cost 500 € Bid Director 1 P11 Plan Cost 500 € Bid Director 1 P11 Actual Cost 700 € Requirements Manager 1 P11 Plan Cost 300 € Requirements Manager 1 P11 Actual Cost 400 € Technical Expert 1 P11 Plan Cost 600 € Technical Expert 1 P12 Actual Cost 900 € Bid Director 2 P12 Plan Cost 700 € Bid Director 2 P12 Actual Cost 1.500 € Requirements Manager 2 P12 Plan Cost 800 € Requirements Manager 2 P12 Actual Cost 900 € Technical Expert 2 P12 Plan Cost 1.400 € Technical Expert 2 P01 Actual Cost 1.200 € Bid Director 3 P01 Plan Cost 900 € Bid Director 3 P01 Actual Cost 2.400 € Requirements Manager 3 P01 Plan Cost 1.500 € Requirements Manager 3 P01 Actual Cost 900 € Technical Expert 3 P01 Plan Cost 1.800 € Technical Expert 3 P02 Actual Cost 1.200 € Bid Director 4 P02 Plan Cost 1.200 € Bid Director 4 P02 Actual Cost 2.400 € Requirements Manager 4 P02 Plan Cost 1.500 € Requirements Manager 4 P02 Actual Cost 900 € Technical Expert 4 P02 Plan Cost 2.300 € Technical Expert 4 P03 Actual Cost 1.200 € Bid Director 5 P03 Plan Cost 1.600 € Bid Director 5 P03 Actual Cost 2.400 € Requirements Manager 5 P03 Plan Cost 1.560 € Requirements Manager 5 P03 Actual Cost 900 € Technical Expert 5 P03 Plan Cost 2.700 € Technical Expert 5
- lbendlin
Super User
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.Dear Ibendlin,
not sure why my response is not shown below your message, but as requested please see the sample data in my other response.
I created a running total on the table where I had pivoted the table (see above at the second table (not the raw one)). Let this be called Off-PeriodsTable
RunningTotal = CALCULATE( SUMX( FILTER( ALL('Off-PeriodsTable'), 'Off-PeriodsTable'[Function] = MAX('Off-PeriodsTable'[Function]) && 'Off-PeriodsTable'[SortOrder] <= MAX('Off-PeriodsTable'[SortOrder]) && 'Off-PeriodsTable'[Cost Type] = MAX('Off-PeriodsTable'[Cost Type]) ), 'Off-PeriodsTable'[Cost] ) )This works partially. When I filter for a function e.g. Bid director the values are correct. But the default stage of the line chart when no filter is selected it shows a wrong total/number. Also when I add the attributes to a table for testing purposes, the Total at the very end is either 0 or way less then expected.
- lbendlin
may want to check your math on the Requirements manager.
