Divide by fixed calculated value, don't calculate for each ROW

[Anonymous]

Hi all,

Let me try to explain an issue I have.

I have a SALARY table like this:

Date	Salary Cost
01-01-2020	1110000
02-01-2020	1110000
03-01-2020	1110000
04-01-2020	1110000
05-01-2020	1110000
06-01-2020	1110000
07-01-2020	1110000
08-01-2020	1110000
09-01-2020	1110000
10-01-2020	1110000
11-01-2020	1110000
12-01-2020	1110000
01-01-2021	1110000
02-01-2021	1110000
03-01-2021	1110000

Then I have a "Project" table, where I have a measure to calculate the "Total Time Spend" per project. The goal is to divide our Salary for each job.

Then I have a measure calculating "Total Time Spend": 

Total Time Spend = DIVIDE(SUM(Salary[Total Cost]),[MinutesSpend])

Here I just want a FIXED number that I can use across all projects.

I want the "Cost per Point (minute)" measure to just be a fixed number per month, meaning it shouldnt calculate on each row when I look at projects.

Example:

Salary for March: 1.110.000

Minutes in March: 229.227

Total Cost Pr. Point= 4.84

So for each row (each project) it should just say [MinutesSpend] * 4.84 ("Total Cost Pr. Point" for that month).

I know that the totals would be the Salary sum, as it is the 3 same numbers we calculate back and forth.

But in my table it is showing for each row fixed 1.110.000 and not the individual numbers..

So in my example, the one with ID 693130, I would like to calculate like:

9 * 4,84 = 43,56 GP2 cost .

Hope you can help!

I have relationship with Calendar table.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please use the following formula :

Avg Cost Measure =
VAR _time =
    CALCULATE (
        SUM ( 'Projects'[Time spend] ),
        FILTER (
            ALL ( 'Projects' ),
            MONTH ( 'Projects'[Date] ) = MONTH ( MAX ( 'Projects'[Date] ) )
        )
    )
VAR _cost =
    CALCULATE (
        MAX ( 'Salary'[Salary Cost] ),
        FILTER (
            'Salary',
            MONTH ( 'Salary'[Date] ) = MONTH ( MAX ( 'Projects'[Date] ) )
        )
    )
RETURN
    DIVIDE ( _cost, _time )

Measure =
MAX ( 'Projects'[Time spend] ) * [Avg Cost Measure]

The final output is shown below:

Here is the pbix file.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi again,

I have a test file:

https://www.dropbox.com/s/duym9jwmsgyt5zd/TEST%20Points.pbix?dl=0

I will try to explain again 🙂

We have a "Total Salary" per month, that we use to calculate cost per project we do in our business.

For each project, we have "Time spend" which are counted as minutes.

So what I need is:

1. The total Salary for the month divided by the Total time spend. That should give me 1 number we can use as "Cost pr. point" (in this case minute).

2. Then I want for each project, to calculate the cost.

In the example I provide in this file, you can see that the "Total Cost pr. Point" for February is 11.42.

This number should then be the fixed number I need to multiply the "Time spend" for each project.

But I can not make it work, as it now just shows me the total for each row:

[Ashish_Mathur]

What result are you expecting?