# Calculate Prorata in table

#### frederic

##### New Member
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.

 Client Driver Activity Period Hours ID Driver Cost A 100 Messages 02-2013 5 02-2013-100 555,55 B 200 Messages 02-2013 3 02-2013-200 750,00 C 100 Messages 02-2013 4 02-2013-100 444,45 D 200 Messages 02-2013 3 02-2013-200 750,00 E 300 Messages 02-2013 7 02-2013-300 2000,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.

 Driver Period Cost ID Driver 100 02-2013 1000 02-2013-100 200 02-2013 1500 02-2013-200 300 02-2013 2000 02-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

Last edited:

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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...

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

This should do it for you:

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

Facts[Hours] )/

CALCULATE( SUM( Facts[Hours] ),

FILTER('Facts',

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

)

)``````

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.

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.

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

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.

Replies
6
Views
358
Replies
2
Views
221
Replies
0
Views
195
Replies
2
Views
214
Replies
1
Views
475

1,196,508
Messages
6,015,615
Members
441,906
Latest member
gafoor

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back