Calculate Prorata in table


New Member
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 DriverCost


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 Driver


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.
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...
Upvote 0
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.


Upvote 0
This should do it for you:

=( RELATED( Driver[Cost] )*

     Facts[Hours] )/

     CALCULATE( SUM( Facts[Hours] ),


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


Upvote 0
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.
Upvote 0
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!
Upvote 0
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
Upvote 0
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.
Upvote 0

Forum statistics

Latest member

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
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
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