# Calculate Prorata in table



## frederic (Apr 15, 2013)

Hello Everybody,

I have two tables with this structure :

table 1: The fact table. The last column is concatenation of Period&"-"&driver. The column cost is the column i want to calculate.


ClientDriverActivityPeriodHoursID DriverCostA100Messages02-2013502-2013-100555,55B200Messages02-2013302-2013-200750,00C100Messages02-2013402-2013-100444,45D200Messages02-2013302-2013-200750,00E300Messages02-2013702-2013-3002000,00

<tbody>

</tbody>
Table 2: The cost table with 1 line per driver. The column ID Driver is concatenation of period&"-"&driver and is linked to the column ID driver of table 1 and the relation works fine.


DriverPeriodCostID Driver10002-2013100002-2013-10020002-2013150002-2013-20030002-2013200002-2013-300

<tbody>

</tbody>
So with this data model, i'd like to compute the cost distribution using the colum cost of table 2.

for example in the first row of table 1 the calculation would be :
[cost]= 1000 x 5 / ( 5 + 4 ) = 555,55
In the second row the calculation is :
[Cost]=1500 x 3 / (3 + 3 ) = 750 and so on.

I'm searching for the definitive formula with no success.

I know there's maybe a solution with adding a table like this one linked in Driver ID but i'd like to avoid this solution which is longer.

Driver ID        Hours
100               9
200               6
300               7

Thank you for your help.


----------



## LxQ (Apr 15, 2013)

If you put the "ID Driver" column before the "Cost" column in Table 2, then you can use a VLOOKUP to get the Cost and multiply that by the hour... and then I'm lost as to what the (5+4) and the (3+3) means and where they come from...


----------



## frederic (Apr 16, 2013)

Thank you LxQ but i think i wasn't clear,

The calculation i want to made will be embed in a powerpivot calculated column, not in Excel sheet where you can use vlookup function.
So i'm lokking for a DAX formula to do a calculation as described in the following screenshot.







Thanks


----------



## MD610 (Apr 16, 2013)

This should do it for you:


```
=( RELATED( Driver[Cost] )*

     Facts[Hours] )/

     CALCULATE( SUM( Facts[Hours] ),

                               FILTER('Facts', 

                                                Facts[ID Driver] = EARLIER( Facts[ID Driver] )

                                      )

                  )
```


----------



## frederic (Apr 17, 2013)

Thank you a lot Mike D,

It's exactly what i was looking for. i tried a lot of tests without any success and now i understand that the important function i have forgotten is the EARLIER function.
I wasn't able to make a test with every value of every row of a specific column. The EARLIER function does that job.

Just another question in the case i have to do a more complicated calculation using more than one filter. In that case, i think i should use the CALCULATETABLE as an input for the SUM function. Or maybe i could use another FILTER function as another CALCULATE filter parameter ? I'll did some test.

Mike D you gave me a very appreciable help, thank you a lot again.


----------



## MD610 (Apr 17, 2013)

Glad I could help!

I don't know that CALCULATETABLE() would work in the context you are specifying or perhaps I don't fully understand your question.  CALCULATETABLE() returns a table as a result and therefore it could not be used directly as an argument in SUM() becasue that only accepts a column.

You could however add additional filters in the second argument of the FILTER() if you needed them.  Just string them together with && (and) or || (or) as needed. The CALCULATE() function will also accept more than one filter criteria separated by commas.  Additional filter arguements in a CALCULATE() all get treated as ANDs.

Good luck with your experimentation!


----------



## frederic (Apr 17, 2013)

Excuse me Mike, i wasn't clear enough. When i said "more than one filter" i was talking about filtering different columns, not different values for one column.
I think that it's possible to use CALCULATETABLE as an input for SUMX function and to specify different filters on different columns in CALCULATETABLE parameters.

Thank you again Mike


----------



## MD610 (Apr 17, 2013)

I see.  Just so you know, you can filter different columns when stringing together multiple parameters in a FILTER() using && and ||.  They don't all have to be values from the same column.


----------

