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: Date Salary Cost 01-01-2020 1110000 02-01-2020 1110000 03-01-2020 1110000 04-01-2020 1...
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.

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:

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

What result are you expecting?