Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

Hi all,

 

Let me try to explain an issue I have.

 

I have a SALARY table like this:

DateSalary Cost
01-01-20201110000
02-01-20201110000
03-01-20201110000
04-01-20201110000
05-01-20201110000
06-01-20201110000
07-01-20201110000
08-01-20201110000
09-01-20201110000
10-01-20201110000
11-01-20201110000
12-01-20201110000
01-01-20211110000
02-01-20211110000
03-01-20211110000

 

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.
 
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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:

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.