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.
Or Anonymous
If you want to show only one row as the SUM of all, just create a measure like this and put it as the new column into the Table Visualization:
% Spent Measure =
DIVIDE(SUM(Percentages[Amount Expended]), SUM(Percentages[Overall Limit]))
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.