Forum Discussion
Percentage Calculation & Sumumarization
Hi Anonymous ,
Calculated column couldn't show dynamic aggregation value like the sum of column 1 divided by the sum of column 2. You see if you create [%Spent] as a calcualted column, it will show you sum/max/ do not sum/ and so on based on the column result. You can try to create a measure to achieve your goal.
Measure % Spent = DIVIDE(SUM('Orders'[Amount Expended]),SUM( 'Orders'[Overall Limit]))Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Here is some sample data. This is what the line items look like. I've selected summarize so I get one line item per order. I get the correct totals for the Amount Expended and Amount Available. The problem is the percentage column, it's adding all of the line items and giving me 2602.78% instead of the percent 87.4% that I'm expecting.
| Order Number | Line Number | Requisition Line Number | Requested by | Product Or Service | Catalog Indicator | Overall Limit | Amount Expended | % Spent | Amount Available |
| 0062178311-01 | 1 | 1 | Doe, John | Product | Catalog | $19 | $17 | 91.32% | $2 |
| 0062178311-01 | 4 | 4 | Doe, John | Product | Catalog | $19 | $17 | 91.34% | $2 |
| 0062178311-01 | 5 | 5 | Doe, John | Product | Catalog | $25 | $23 | 91.31% | $2 |
| 0062178311-01 | 1 | 1 | Doe, John | Product | Catalog | $19 | $17 | 91.32% | $2 |
| 0062178311-01 | 4 | 4 | Doe, John | Product | Catalog | $19 | $17 | 91.34% | $2 |
| 0062178311-01 | 5 | 5 | Doe, John | Product | Catalog | $25 | $23 | 91.31% | $2 |
| 0062178311-01 | 1 | 1 | Doe, John | Product | Catalog | $19 | $17 | 91.32% | $2 |
| 0062178311-01 | 4 | 4 | Doe, John | Product | Catalog | $19 | $17 | 91.34% | $2 |
| 0062178311-01 | 5 | 5 | Doe, John | Product | Catalog | $25 | $23 | 91.31% | $2 |
| 0062178311-01 | 1 | 1 | Doe, John | Product | Catalog | $19 | $17 | 91.32% | $2 |
| 0062178311-01 | 4 | 4 | Doe, John | Product | Catalog | $19 | $17 | 91.34% | $2 |
| 0062178311-01 | 5 | 5 | Doe, John | Product | Catalog | $25 | $23 | 91.31% | $2 |
| 0062178311-01 | 1 | 1 | Doe, John | Product | Catalog | $19 | $17 | 91.37% | $2 |
| 0062178311-01 | 4 | 4 | Doe, John | Product | Catalog | $19 | $17 | 91.25% | $2 |
| 0062178311-01 | 5 | 5 | Doe, John | Product | Catalog | $25 | $23 | 91.38% | $2 |
| 0062178311-01 | 3 | 3 | Doe, John | Product | Non-Catalog | $41 | $26 | 63.92% | $15 |
| 0062178311-01 | 2 | 2 | Doe, John | Product | Non-Catalog | $114 | $104 | 91.33% | $10 |
| 0062178311-01 | 6 | 6 | Doe, John | Product | Non-Catalog | $65 | $59 | 91.33% | $6 |
| 0062178311-01 | 3 | 3 | Doe, John | Product | Non-Catalog | $41 | $26 | 63.92% | $15 |
| 0062178311-01 | 2 | 2 | Doe, John | Product | Non-Catalog | $114 | $104 | 91.33% | $10 |
| 0062178311-01 | 6 | 6 | Doe, John | Product | Non-Catalog | $65 | $59 | 91.33% | $6 |
| 0062178311-01 | 3 | 3 | Doe, John | Product | Non-Catalog | $41 | $26 | 63.92% | $15 |
| 0062178311-01 | 2 | 2 | Doe, John | Product | Non-Catalog | $114 | $104 | 91.33% | $10 |
| 0062178311-01 | 6 | 6 | Doe, John | Product | Non-Catalog | $65 | $59 | 91.33% | $6 |
| 0062178311-01 | 3 | 3 | Doe, John | Product | Non-Catalog | $41 | $26 | 63.92% | $15 |
| 0062178311-01 | 2 | 2 | Doe, John | Product | Non-Catalog | $114 | $104 | 91.33% | $10 |
| 0062178311-01 | 6 | 6 | Doe, John | Product | Non-Catalog | $65 | $59 | 91.33% | $6 |
| 0062178311-01 | 3 | 3 | Doe, John | Product | Non-Catalog | $41 | $26 | 63.94% | $15 |
| 0062178311-01 | 2 | 2 | Doe, John | Product | Non-Catalog | $114 | $104 | 91.31% | $10 |
| 0062178311-01 | 6 | 6 | Doe, John | Product | Non-Catalog | $65 | $59 | 91.33% | $6 |
| 2602.78% |