Percentage Calculation & Sumumarization

[work_1111]

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%.

[work_1111]

@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.

[work_1111]

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%

[work_1111]

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%

[vojtechsima]

Hi, @work_1111 
I think simple 'Don't summarize' could do it, if you have your % spent as Calculated column

[vojtechsima]

Or @work_1111 
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]))

[work_1111]

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

[work_1111]

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

[v-rzhou-msft · Accepted solution]

Hi @work_1111 ,

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vojtechsima]

Hi, @work_1111 
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 you

[work_1111]

I tried playing around with the MAX function but it didn't resolve the issue.

[vojtechsima]

@work_1111 
Please share a sample dataset (copyable).