Power Pivot Many to Many Pivot Relationship Tables

[willstein818]

Hi Everyone,

So I'm currently making a dashboard that shows sales achievements per Customer Name compared to their targets.
In the 'Raw' table, I have created the Baseline & Actual for each Customer, while the 'Sales Target' table shows the Monthly Target per Customer, I also have created bridge tables to make a Many to Many pivot table relationships. (Power Pivot relationship usage)

The Steps that I will take are as follows:
1. Make a 'BaselineTotal' (Sum of all baseline sales in each 'Account')
2. use Divide measures ('Sum of Baseline' / 'Baseline Total') to get Customer Name Percentage (Sales Percentage contribution of each customers inside the account)
3. Use the Customer Name Percentage*Sum of Target Qty to get Specified Sales Target for each Customer Name.
4. Compare the target to the 'Actual' and get total achievement.

I'm stuck at the first step, in which the 'BaselineTotal' didn't show the correct numbers for each 'Account'. Moreover, the formula has created an 'error' in which Customer Name appears at the wrong 'Account', even though i have filtered it using slicers (All slicers are from 'Unique' Table)

Is there any solution to make this dashboard happen? What did I do wrong at the Pivot Table Relationships? Thanks a lot for your help.

Regards,
William

[rwfigtree]

no expert but think this solves 1 and 2, not to good on the two fact table thing yet so cant help with rest.

mBaseline:=SUM(Raw[Baseline])+0
mBaseLineAcctTotal:=CALCULATE([mBaseline],ALL(Raw[Customer Name]))
mBaseLine%:=divide([mBaseline],[mBaselineAcctTotal])

get rid of the "+0" on first measure if you don't want all the zero sales showing up.

[rwfigtree]

Continuing from previous post I think this works for the parts 3 and 4.
A virtual relationship. Haven't checked just learnt this myself.
Anyway was fun trying to figure this out.

mSalesTarget:=SUM(SalesTarget[Target Qty (Pcs)])

mTarget:=CALCULATE(sum(SalesTarget[Target Qty (Pcs)]),
FILTER(all(SalesTarget[Account]),CONTAINS(VALUES(Raw[Account]),Raw[Account],SalesTarget[Account])),
FILTER(all(SalesTarget[Type]),CONTAINS(VALUES(Raw[Type]),Raw[Type],SalesTarget[Type])),
FILTER(all(SalesTarget[SKU]),CONTAINS(VALUES(Raw[SKU]),Raw[SKU],SalesTarget[SKU]))
)

mCustTarget:=[mBaseLine%]*[mTarget]
mActual:=SUM(Raw[Actual])+0
mCustAchievement:=DIVIDE([mActual],[mCustTarget])