Forum Discussion
% Margin between rows
Hi!
I have searched for hours for this solution without success, please help me before I go mad. I have a set of data for all my projects. The data contains columns such as labour cost, material cost etc etc.
I wont to calculate the Sales and Production % margin for Project 1 and 2 and 3 and so on.
Is it possible to calculate rows with eachother?
Calculation | Project | Labour cost | Machining Cost | Material Cost |
Sales Calc Project 1 200 000 80 000 300 000
Production Calc Project 1 150 000 50 000 400 000
Forecast Calc Project 1 140 000 120 000 250 000
4 Replies
Is the graphic below an accurate representation of your data table? If so, you have a few options, but I would start by creating a MEASURE using SUMX and using that MEASURE in a CALCULATE function and use a FILTER to apply our measure to the Project you are working.
1. Total Sales := SUMX ( Table1, Table1[Labour cost] + Table1[Machining Cost] + Table1[Material Cost] )
2. Total Sales by Project :=
CALCULATE (
[Total Sales],
Table1[Project] = "Project 1"
)
I am not 100% what the Production % calculation needs to be, but I would assume (Labour cost + Machining Cost) / Total Sales, if so:
3. Production Cost := SUMX ( Table1, Table1[Labour cost] + Table1[Machining Cost] )
4. Production Percentage := DIVIDE ( [Production Cost], [Total Sales] )
5. Production % by Project :=
CALCULATE (
[Production Percentage],
Table1[Project] = "Project 1"
)
I guess I wasnt clear enough.
For each distinct project I want to do the following calculation.
(Sales Calculation of the labour cost -(minus) Forecast calculation of the labour cost) /(divided by) Sales calculation of the labour cost.
This will give me the sales margin and the same calculation goes for Production calculation.
Are the forecasted values stored in the same table snippet you originally showed? Can you share an actual slice of your data table and I can mock this up in Power Bi?
1. Labour Cost := SUMX ( Table1, [Labour Cost] )
2. Forecast Labour := SUMX ( Table1, [Forecast Labour] ) ** Unsure of your actual column name for forecasted labour
3. Sales Margin := DIVIDE ( [Labour Cost] - [Forecast Labour], [Labour Cost] )
4. If you are using the [Sales Margin] value in a visualization, you can apply this measure over a visual filtered by the 'Project' column. This would give you the total 'Sales Margin' for the Project 1, 2, 3, etc.
