PowerPivot - Measures/Columns

Manu Grewal

New Member
Joined
Dec 28, 2023
Messages
2
Office Version
  1. 365
Platform
  1. 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:
LocationCodeCommACommPPerAPerPCommTotalPerTotal
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:
CodeAmount
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]
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

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.
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 "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
Back
Top