Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
work_1111
Helper II
Helper II

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

a626a999-9b5e-4d03-8a5d-47eebf28ec37.png

  

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

RicoZhou_0-1649930423779.png

 

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.

 

View solution in original post

11 REPLIES 11
work_1111
Helper II
Helper II

@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
Helper II
Helper II

Order NumberLine NumberRequisition Line NumberRequested byProduct Or ServiceCatalog IndicatorOverall LimitAmount Expended % SpentAmount Available
0062178311-0111Doe, JohnProductCatalog$19$1791.32%$2
0062178311-0144Doe, JohnProductCatalog$19$1791.34%$2
0062178311-0155Doe, JohnProductCatalog$25$2391.31%$2
0062178311-0111Doe, JohnProductCatalog$19$1791.32%$2
0062178311-0144Doe, JohnProductCatalog$19$1791.34%$2
0062178311-0155Doe, JohnProductCatalog$25$2391.31%$2
0062178311-0111Doe, JohnProductCatalog$19$1791.32%$2
0062178311-0144Doe, JohnProductCatalog$19$1791.34%$2
0062178311-0155Doe, JohnProductCatalog$25$2391.31%$2
0062178311-0111Doe, JohnProductCatalog$19$1791.32%$2
0062178311-0144Doe, JohnProductCatalog$19$1791.34%$2
0062178311-0155Doe, JohnProductCatalog$25$2391.31%$2
0062178311-0111Doe, JohnProductCatalog$19$1791.37%$2
0062178311-0144Doe, JohnProductCatalog$19$1791.25%$2
0062178311-0155Doe, JohnProductCatalog$25$2391.38%$2
0062178311-0133Doe, JohnProductNon-Catalog$41$2663.92%$15
0062178311-0122Doe, JohnProductNon-Catalog$114$10491.33%$10
0062178311-0166Doe, JohnProductNon-Catalog$65$5991.33%$6
0062178311-0133Doe, JohnProductNon-Catalog$41$2663.92%$15
0062178311-0122Doe, JohnProductNon-Catalog$114$10491.33%$10
0062178311-0166Doe, JohnProductNon-Catalog$65$5991.33%$6
0062178311-0133Doe, JohnProductNon-Catalog$41$2663.92%$15
0062178311-0122Doe, JohnProductNon-Catalog$114$10491.33%$10
0062178311-0166Doe, JohnProductNon-Catalog$65$5991.33%$6
0062178311-0133Doe, JohnProductNon-Catalog$41$2663.92%$15
0062178311-0122Doe, JohnProductNon-Catalog$114$10491.33%$10
0062178311-0166Doe, JohnProductNon-Catalog$65$5991.33%$6
0062178311-0133Doe, JohnProductNon-Catalog$41$2663.94%$15
0062178311-0122Doe, JohnProductNon-Catalog$114$10491.31%$10
0062178311-0166Doe, JohnProductNon-Catalog$65$5991.33%$6
        2602.78% 
work_1111
Helper II
Helper II

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 NumberLine NumberRequisition Line NumberRequested byProduct Or ServiceCatalog IndicatorOverall LimitAmount Expended % SpentAmount Available
0062178311-0111Doe, JohnProductCatalog$19$1791.32%$2
0062178311-0144Doe, JohnProductCatalog$19$1791.34%$2
0062178311-0155Doe, JohnProductCatalog$25$2391.31%$2
0062178311-0111Doe, JohnProductCatalog$19$1791.32%$2
0062178311-0144Doe, JohnProductCatalog$19$1791.34%$2
0062178311-0155Doe, JohnProductCatalog$25$2391.31%$2
0062178311-0111Doe, JohnProductCatalog$19$1791.32%$2
0062178311-0144Doe, JohnProductCatalog$19$1791.34%$2
0062178311-0155Doe, JohnProductCatalog$25$2391.31%$2
0062178311-0111Doe, JohnProductCatalog$19$1791.32%$2
0062178311-0144Doe, JohnProductCatalog$19$1791.34%$2
0062178311-0155Doe, JohnProductCatalog$25$2391.31%$2
0062178311-0111Doe, JohnProductCatalog$19$1791.37%$2
0062178311-0144Doe, JohnProductCatalog$19$1791.25%$2
0062178311-0155Doe, JohnProductCatalog$25$2391.38%$2
0062178311-0133Doe, JohnProductNon-Catalog$41$2663.92%$15
0062178311-0122Doe, JohnProductNon-Catalog$114$10491.33%$10
0062178311-0166Doe, JohnProductNon-Catalog$65$5991.33%$6
0062178311-0133Doe, JohnProductNon-Catalog$41$2663.92%$15
0062178311-0122Doe, JohnProductNon-Catalog$114$10491.33%$10
0062178311-0166Doe, JohnProductNon-Catalog$65$5991.33%$6
0062178311-0133Doe, JohnProductNon-Catalog$41$2663.92%$15
0062178311-0122Doe, JohnProductNon-Catalog$114$10491.33%$10
0062178311-0166Doe, JohnProductNon-Catalog$65$5991.33%$6
0062178311-0133Doe, JohnProductNon-Catalog$41$2663.92%$15
0062178311-0122Doe, JohnProductNon-Catalog$114$10491.33%$10
0062178311-0166Doe, JohnProductNon-Catalog$65$5991.33%$6
0062178311-0133Doe, JohnProductNon-Catalog$41$2663.94%$15
0062178311-0122Doe, JohnProductNon-Catalog$114$10491.31%$10
0062178311-0166Doe, JohnProductNon-Catalog$65$5991.33%$6
        2602.78% 

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

vojtechsima_0-1649778370629.png

 

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]))

vojtechsima_1-1649778650634.png

 



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 NumberLine NumberRequisition Line NumberRequested byProduct Or ServiceCatalog IndicatorOverall LimitAmount Expended % SpentAmount Available
0062178311-0111Doe, JohnProductCatalog$19$1791.32%$2
0062178311-0144Doe, JohnProductCatalog$19$1791.34%$2
0062178311-0155Doe, JohnProductCatalog$25$2391.31%$2
0062178311-0111Doe, JohnProductCatalog$19$1791.32%$2
0062178311-0144Doe, JohnProductCatalog$19$1791.34%$2
0062178311-0155Doe, JohnProductCatalog$25$2391.31%$2
0062178311-0111Doe, JohnProductCatalog$19$1791.32%$2
0062178311-0144Doe, JohnProductCatalog$19$1791.34%$2
0062178311-0155Doe, JohnProductCatalog$25$2391.31%$2
0062178311-0111Doe, JohnProductCatalog$19$1791.32%$2
0062178311-0144Doe, JohnProductCatalog$19$1791.34%$2
0062178311-0155Doe, JohnProductCatalog$25$2391.31%$2
0062178311-0111Doe, JohnProductCatalog$19$1791.37%$2
0062178312-0144Doe, JohnProductCatalog$19$1791.25%$2
0062178312-0155Doe, JohnProductCatalog$25$2391.38%$2
0062178312-0133Doe, JohnProductNon-Catalog$41$2663.92%$15
0062178312-0122Doe, JohnProductNon-Catalog$114$10491.33%$10
0062178312-0166Doe, JohnProductNon-Catalog$65$5991.33%$6
0062178312-0133Doe, JohnProductNon-Catalog$41$2663.92%$15
0062178312-0122Doe, JohnProductNon-Catalog$114$10491.33%$10
0062178312-0166Doe, JohnProductNon-Catalog$65$5991.33%$6
0062178312-0133Doe, JohnProductNon-Catalog$41$2663.92%$15
0062178312-0122Doe, JohnProductNon-Catalog$114$10491.33%$10
0062178312-0166Doe, JohnProductNon-Catalog$65$5991.33%$6
0062178312-0133Doe, JohnProductNon-Catalog$41$2663.92%$15
0062178312-0122Doe, JohnProductNon-Catalog$114$10491.33%$10
0062178312-0166Doe, JohnProductNon-Catalog$65$5991.33%$6
0062178312-0133Doe, JohnProductNon-Catalog$41$2663.94%$15
0062178312-0122Doe, JohnProductNon-Catalog$114$10491.31%$10
0062178312-0166Doe, JohnProductNon-Catalog$65$5991.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.

RicoZhou_0-1649930423779.png

 

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
Resident Rockstar
Resident Rockstar

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.

@work_1111 
Please share a sample dataset (copyable).

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.