Forum Discussion
Percentage Calculation & Sumumarization
Hello,
I have a table for purchase order data. I created a column with the following calculation to get the '%Spent'. [ % Spent] = DIVIDE('Orders'[Amount Expended], 'Orders'[Overall Limit]) . There can be 10 order entries for 1 purchase order and the %Spent column is adding the percentage for each order and resulting in like 2000% spend. How can I fix this so the table will not add up each percentage for each line item, it should just say 90%.
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.
11 Replies
Hi, Anonymous
You can use MAX function to take only one value for your Order.
If you want a tailor-made solution, please share a copyable sample dataset. Thank youAnonymous
Please share a sample dataset (copyable).
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% Hi, Anonymous
I think simple 'Don't summarize' could do it, if you have your % spent as Calculated columnOr 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]))
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% vojtechsima please see sample data set above in post. The 'Overall Limit' column totals correctly and so does the 'Amount Expended'. The percentage column is technically adding each line item but I don't want it to do that, I want it to take the percentage on the total Amount Expended for an order and Overall Limit.
