Running Total

[Anonymous]

I've been struggling with the following and I can't quite figure out what's going wrong. We've only recently started working with PowerBi so I still have to learn a lot.

In our greenhouses the employees are cutting fruit. We want to know how each employee compares to the other employees in terms of productivity and cost. We have data about how much fruit (kg) each person has cut,  and what their salarycost is, in a single table. 

At first I calculate, with a few measures, for each day and for each greenhouse what the avg cost per kg is.

KGSUM = CALCULATE( SUM(table[Kg]) ;ALLEXCEPT(table;table[Task];table[Date];table[Location]) )

CostSUM = CALCULATE( SUM(table[Cost]) ;ALLEXCEPT(table;table[Task];table[Date];table[Location]) )

Cost_per_KG_Total = 

VAR __CATEGORY_VALUES = VALUES(table[Date])

RETURN

    DIVIDE(

        SUMX(

            KEEPFILTERS(__CATEGORY_VALUES);

            CALCULATE(SUMX(table;table[CostSUM]))

        );

        SUMX(

            KEEPFILTERS(__CATEGORY_VALUES);

            CALCULATE(SUMX(table;table[KGSUM]))

        )

    )

Then I calculate for each employee what their cost/kg is.

Cost_per_KG_Employee = 

DIVIDE(sum(table[Cost]);sum(table[Kg]))

Then I can calculate what their "profit" per KG is. By multiplying the "profit" per KG by the amount of KG they cut.

Total_Profit = sum(table[Kg])*(table[Cost_per_KG_Total]-table[Cost_per_KG_Employee])

The result per day per employee is actually correct. However when I sum the Total_profit for a single employee, the total doesn't show the value I would expect. Also my running total measure doesn't show the values I want to see so I must be doing something wrong there as well. However maybe the running total is fixed when the Total_profit is fixed.

Running Total = 

CALCULATE (

[Total_Profit];

FILTER (

ALL ( table[Date] ); table[Date] <= MAX ( table[Date] )

            )

)

 Shows:

TotalProfit          RunningTotal

-15                     -15

27                      11(you would expect 12)

12                       23

27                      40 (expect 50)

44                      78 (you would expect 84)

Sorry for the wall of text, I hope someone is able to help me.

Also I might be doing things inefficiently, tips about doing things differently are always welcome.

Below is what the data looks like, I gave the employees a color

Example

Thanks in advance! Hope I can someday return the favor to the community.

[CheenuSing]

Hi @Anonymous ,

Can you upload sampledata, pbix and output expected to Google/One Drive and share the link here to find a solution.

Cheers

CheenuSing