% Margin between rows

[Anonymous]

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

[Anonymous]

Basically the difference between two values on the same column based on project number.

[JBaker0585]

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"

)

[Anonymous]

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.

[JBaker0585]

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.