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.
So there are thousands of other orders, I want the percentage for each order. In the sample data set, I only included the line items for 1 order. Imagine the same situation but with differnt order numbers as well. I've updated the sample data set from the previous post
| 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 |
| 0062178312-01 | 4 | 4 | Doe, John | Product | Catalog | $19 | $17 | 91.25% | $2 |
| 0062178312-01 | 5 | 5 | Doe, John | Product | Catalog | $25 | $23 | 91.38% | $2 |
| 0062178312-01 | 3 | 3 | Doe, John | Product | Non-Catalog | $41 | $26 | 63.92% | $15 |
| 0062178312-01 | 2 | 2 | Doe, John | Product | Non-Catalog | $114 | $104 | 91.33% | $10 |
| 0062178312-01 | 6 | 6 | Doe, John | Product | Non-Catalog | $65 | $59 | 91.33% | $6 |
| 0062178312-01 | 3 | 3 | Doe, John | Product | Non-Catalog | $41 | $26 | 63.92% | $15 |
| 0062178312-01 | 2 | 2 | Doe, John | Product | Non-Catalog | $114 | $104 | 91.33% | $10 |
| 0062178312-01 | 6 | 6 | Doe, John | Product | Non-Catalog | $65 | $59 | 91.33% | $6 |
| 0062178312-01 | 3 | 3 | Doe, John | Product | Non-Catalog | $41 | $26 | 63.92% | $15 |
| 0062178312-01 | 2 | 2 | Doe, John | Product | Non-Catalog | $114 | $104 | 91.33% | $10 |
| 0062178312-01 | 6 | 6 | Doe, John | Product | Non-Catalog | $65 | $59 | 91.33% | $6 |
| 0062178312-01 | 3 | 3 | Doe, John | Product | Non-Catalog | $41 | $26 | 63.92% | $15 |
| 0062178312-01 | 2 | 2 | Doe, John | Product | Non-Catalog | $114 | $104 | 91.33% | $10 |
| 0062178312-01 | 6 | 6 | Doe, John | Product | Non-Catalog | $65 | $59 | 91.33% | $6 |
| 0062178312-01 | 3 | 3 | Doe, John | Product | Non-Catalog | $41 | $26 | 63.94% | $15 |
| 0062178312-01 | 2 | 2 | Doe, John | Product | Non-Catalog | $114 | $104 | 91.31% | $10 |
| 0062178312-01 | 6 | 6 | Doe, John | Product | Non-Catalog | $65 | $59 | 91.33% | $6 |
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.