Forum Discussion
Calculate Profit Margin %
I'm struggling to calculate profit margin %.
Before summation of % Profit Margin
SO Dock Date Sum of COGS Sum of Total Price Sum of Total Profits % Profit Margin
961 4/8/2020 $8,074.41 $2,982.00 ($5,092.41) -170.77%
961 4/8/2020 $0.00 $10,437.00 $10,437.00 100.00%
After summation of % Profit Margin
SO Dock Date Sum of COGS Sum of Total Price Total Profit Sum of % Profit Margin
961 4/8/2020 $8,074.41 $13,419.00 5,344.59 -70.77%
As you can see the profit margin is not correct.
I have a SO with multiple line items on it. I have summed up the SO line to calculate COGS, Total Price of the assembly. Then, I created a measure to calculate profit.
Total Profit = [Total Price] - [COGS]
% Profit Margin = Divide([Total Profit], [Total Price], 0)
I have solved this by adjusting my query and pulling data in another way. Please disregard.
8 Replies
- amitchandak
Super User
kblevs ,
I am hoping those are measures and not columns and formula is Actually
% Profit Margin = Divide(sum(Table[Total Profit]), sum(Table[Total Price]), 0)
They are all columns except for Total Profits. I have already tried to sum total profits and total price. Here are those results.
% Profit Margin = DIVIDE(SUM(Query1[Total Profit]), SUM(Query1[Total Price]),0)
SO Dock Date Sum of COGS Sum of Total Price Sum of Total Profits % Profit Margin
961 4/8/2020 $8,074.41 $13,419.00 $5,344.59 40.02%That actually gives me 40.02% for every row.
Hi kblevs,
Is this formula nested with multiple measure formulas or direct calculated with table fields? Can you please share some dummy data and measure formulas with a similar data structure to test? It will help us clarify your score and test to coding formula.
How to Get Your Question Answered Quickly
In addition, your scenario looks like a common measure total calculation issue, perhaps you can take a look at the following link about measure total issues:
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
Hi Anonymous
COGS column is a conditional column to make sure that cogs are only calculated once for each SO. Otherwise, the raw data (cost column) would multiply with the multiple lines on a SO. But, the COGS only goes against the SO once.
Line SO Assembly QTY Dock Date Unit Price Total Price cost COGS Total Profit 1 1053 303380 59 4/6/2020 33.00 1947.00 2058.94 2058.94 -111.94 2 1053 TOOLING 1 4/6/2020 300.00 300.00 2058.94 0.00 300.00 3 1053 STENCIL 1 4/6/2020 225.00 225.00 2058.94 0.00 225.00 4 1053 EXPEDITE FEE 1 4/6/2020 696.60 696.60 2058.94 0.00 696.60 5 1053 303380 1 4/6/2020 33.00 33.00 2058.94 0.00 33.00 1 1054 303370 59 4/7/2020 228.75 13496.25 11158.81 11158.81 2337.44 2 1054 TOOLING 1 4/7/2020 300.00 300.00 11158.81 0.00 300.00 3 1054 STENCIL 1 4/7/2020 225.00 225.00 11158.81 0.00 225.00 4 1054 EXPEDITE FEE 1 4/7/2020 2981.40 2981.40 11158.81 0.00 2981.40 5 1054 303370 1 4/7/2020 228.75 228.75 11158.81 0.00 228.75 1 1058 5945.576.18 21 4/15/2020 56.50 1186.50 460.58 460.58 725.92 1 1071 400520 25 4/24/2020 66.00 1650.00 977.12 977.12 672.88 1 944 400956 288 4/2/2020 46.75 13464.00 7276.18 7276.18 6187.82 1 945 400956 288 4/22/2020 46.75 13464.00 7282.56 7282.56 6181.44 1 950 CT 41315 56 3/18/2020 47.50 2660.00 9166.41 9166.41 -6506.41 1 951 CT 49646 216 4/8/2020 22.25 4806.00 2915.13 2915.13 1890.87 1 952 CT 49652 60 3/25/2020 27.50 1650.00 1023.34 1023.34 626.66 1 958 CT 41305 48 4/15/2020 15.00 720.00 382.09 382.09 337.91 1 959 CT 47790 22 4/15/2020 43.00 946.00 558.58 558.58 387.42 1 960 CT 41314 108 4/1/2020 37.25 4023.00 2489.17 2489.17 1533.83 1 961 CT 53448 56 4/8/2020 53.25 2982.00 8074.41 8074.41 -5092.41 2 961 CT 53448 196 4/8/2020 53.25 10437.00 8074.41 0.00 10437.00 Bump
Does anyone have any other suggestions? I have not been able to get this to work.
