Forum Discussion
Solved
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 ...
I have solved this by adjusting my query and pulling data in another way. Please disregard.
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 |
Hi kblevs,
You can try to use below measure formula if it suitable for your requirement:
Measure =
VAR summary =
SUMMARIZE (
table,
[SO],
[Dock Date],
"Sum of Total", SUM ( table[Total Price] ),
"sum of Profit", [Total Profit]
)
RETURN
DIVIDE (
SUMX ( summary, [Sum of Total] ),
SUMX ( summary, [sum of Profit] ),
0
)
Regards,
Xiaoxin Sheng
I have solved this by adjusting my query and pulling data in another way. Please disregard.