Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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%.
Solved! Go to 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 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.
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% |
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, @work_1111
I think simple 'Don't summarize' could do it, if you have your % spent as Calculated column
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]))
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 @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.
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
I tried playing around with the MAX function but it didn't resolve the issue.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
95 | |
86 | |
78 | |
66 |
User | Count |
---|---|
151 | |
121 | |
111 | |
107 | |
95 |