Forum Discussion
% Margin between rows
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.