Calculate Prorata in table

[frederic]

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

Thank you for your help.

 

[LxQ]

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]

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]

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]

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]

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]

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]

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.