Manu Grewal
New Member
- Joined
- Dec 28, 2023
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
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:
Table2:
I have a relationship that Table1
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
Code:
= 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]