• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Pivot Many to Many Pivot Relationship Tables

W

Willstein818

New Member
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
 

Attachments

  • Sample2.xlsx
    979 KB · Views: 2
There is data ambiguity in your relationship.

If you look at your relationship diagram. There is no path that will relate Customer Name of [Raw] to [Account] of [SalesTarget].

I.E. There isn't Customer level definition in [SalesTarget].

Thus [Sum of Target Qty (Pcs)] is calculated for all account = "Redana" and SKU = "DWL 800 mL".
 
Hi Chihiro,

Indeed, I admit that there is no path that relate Customer Level definition in [SalesTarget], but thats because the target are given in total sum, and my goal is to break down those sum Target Qty each month into each CustomerName. To make this Path, I make UniqueCust table linked to UniqueAccount to further define the Customer Level of both Raw and Sales Target, but it doesn't seem to work in this case. Is there something that I could do to fix this problem?


72864
 
There still is no relationship between Sales Target and customer level definition.

You will either need to create custom DAX that will allocate Target Qty to individual customer (by weight) or create another table to do that.
 
Could you give me an example of this custom DAX tables, or at least a reference for me to learn into?

Meanwhile, isn't the relationship between Sales Target and Customer Level definition have been bridge by table UniqueAccount and UniqueCust. I didn't link SalesTarget and UniqueCust directly because the 'Account' in both tables are many to many relationship.

Regards,
William
 
No matter what the relationship you define, there isn't level for Customer info in your Sales Target table. So no, there is no way to allocate values for Sales target into individual customer.

What I mean is you need to define what weight each customer should carry, and then just divide/allocate total Sales Target for Account based on that.

There are not enough info for me to really help you out here, as there isn't enough info in your data set to give you answer.
 
You must log in or register to reply here.
Back
Top