PowerPivot - Measures/Columns

[Manu Grewal]

Hello!
I need to determine the amount for each of the code+location combination across the four columns.
The Total in the Pivot should be equal to the Total Amount in Table2.
The measure should provide the breakdown by location / by code.

Any help would be appreciated.

Regards!

Table1:

Location	Code	CommA	CommP	PerA	PerP	CommTotal	PerTotal
Brampton	1001​	20000​	20000​	30000​	30000​	40000​	60000​
Toronto	1001​	20003​	20003​	30003​	30003​	40006​	60006​
Brampton	1002​	20001​	20001​	30001​	30001​	40002​	60002​
Caledon	1002​	20004​	20004​	30004​	30004​	40008​	60008​
Brampton	1003​	20002​	20002​	30002​	30002​	40004​	60004​
Brampton	1004​	20008​	20008​	30008​	30008​	40016​	60016​
Toronto	1006​	20005​	20005​	30005​	30005​	40010​	60010​
Caledon	1007​	20006​	20006​	30006​	30006​	40012​	60012​
Oakville	1008​	20007​	20007​	30007​	30007​	40014​	60014​

Table2:

Code	Amount
1001​	5000​
1002​	5001​
1003​	5002​
1004​	5003​
1006​	5005​
1007​	5006​
1008​	5007​

I have a relationship that Table1

 = Table2[Code]

In PowerPivot, I have created 8 columns; two of them are below...rest are similar. 
[B]RatioCommA[/B]= 

    DIVIDE(

        (Table1[CommA])  ,

        (Table1[CommTotal]) + (Table1[PerTotal]) ,

        0

    )

And the [B]Amount Column:[/B]
= 

    DIVIDE(SUMX(

        FILTER(Table2, Table2[Code] = Table1[Code]),

        Table2[Amount] * Table1[RatioCommA]),

    COUNTX(Table1,[Location]),

    0

    )

The output is not 100% accurate, as the totals do not match the total in Table2[Amount]

If we can figure out the logic for CommA1, then i can do for the other 4.

[TABLE]
[TR]
[TH]Location[/TH]
[TH]Code[/TH]
[TH]CommA[/TH]
[TH]CommP[/TH]
[TH]PerA[/TH]
[TH]PerP[/TH]
[TH]CommTotal[/TH]
[TH]PerTotal[/TH]
[TH]RatioCommA[/TH]
[TH]CommA1[/TH]
[/TR]
[TR]
[TD]Brampton[/TD]
[TD]1001[/TD]
[TD]20000[/TD]
[TD]20000[/TD]
[TD]30000[/TD]
[TD]30000[/TD]
[TD]40000[/TD]
[TD]60000[/TD]
[TD]0.20[/TD]
[TD]111.11[/TD]
[/TR]
[TR]
[TD]Toronto[/TD]
[TD]1001[/TD]
[TD]20003[/TD]
[TD]20003[/TD]
[TD]30003[/TD]
[TD]30003[/TD]
[TD]40006[/TD]
[TD]60006[/TD]
[TD]0.20[/TD]
[TD]111.11[/TD]
[/TR]
[TR]
[TD]Brampton[/TD]
[TD]1002[/TD]
[TD]20001[/TD]
[TD]20001[/TD]
[TD]30001[/TD]
[TD]30001[/TD]
[TD]40002[/TD]
[TD]60002[/TD]
[TD]0.20[/TD]
[TD]111.13[/TD]
[/TR]
[TR]
[TD]Caledon[/TD]
[TD]1002[/TD]
[TD]20004[/TD]
[TD]20004[/TD]
[TD]30004[/TD]
[TD]30004[/TD]
[TD]40008[/TD]
[TD]60008[/TD]
[TD]0.20[/TD]
[TD]111.14[/TD]
[/TR]
[TR]
[TD]Brampton[/TD]
[TD]1003[/TD]
[TD]20002[/TD]
[TD]20002[/TD]
[TD]30002[/TD]
[TD]30002[/TD]
[TD]40004[/TD]
[TD]60004[/TD]
[TD]0.20[/TD]
[TD]111.16[/TD]
[/TR]
[TR]
[TD]Brampton[/TD]
[TD]1004[/TD]
[TD]20008[/TD]
[TD]20008[/TD]
[TD]30008[/TD]
[TD]30008[/TD]
[TD]40016[/TD]
[TD]60016[/TD]
[TD]0.20[/TD]
[TD]111.19[/TD]
[/TR]
[TR]
[TD]Toronto[/TD]
[TD]1006[/TD]
[TD]20005[/TD]
[TD]20005[/TD]
[TD]30005[/TD]
[TD]30005[/TD]
[TD]40010[/TD]
[TD]60010[/TD]
[TD]0.20[/TD]
[TD]111.23[/TD]
[/TR]
[TR]
[TD]Caledon[/TD]
[TD]1007[/TD]
[TD]20006[/TD]
[TD]20006[/TD]
[TD]30006[/TD]
[TD]30006[/TD]
[TD]40012[/TD]
[TD]60012[/TD]
[TD]0.20[/TD]
[TD]111.25[/TD]
[/TR]
[TR]
[TD]Oakville[/TD]
[TD]1008[/TD]
[TD]20007[/TD]
[TD]20007[/TD]
[TD]30007[/TD]
[TD]30007[/TD]
[TD]40014[/TD]
[TD]60014[/TD]
[TD]0.20[/TD]
[TD]111.27[/TD]
[/TR]
[/TABLE]