Help with matrix for 3 tables

[Anonymous]

Hi,

So i have two tables (backlog and sales), they are joined by another table for sales region and unified customer.  Then created the matrix by unified customer and the value in total works.   But I want to add a layer under for item number, but when i pull the item from sales the backlog $ is wrong, and when i pull item number from backlog, the sales $ is wrong.  

Would like to know how to get around this so the item number shows both $ correctly. 

Thank you. 

[Anonymous]

Hi,

So i have created an excel file breaking down what i'm trying to do. 

The goal is to move my excel driven sales scorecard to power BI. 

The scorecard measures how our actuals are landing against our target.  By sales region, then by sales rep.  Seems easy. 

Right now there are 3 fact tables:  Shipments, Backlog, and AOP. 

Right now there are 2 dimensions: Customer and Date, (customer tells me what region and sales rep, date is the fiscal calendar/period).

FYI - all data is dummy numbers... 

So the tab scorecard is what i'm trying to get to.  As i broke this down, i really think the issue starts with my fiscal period as it relates to past due. 

Example.  the 2020-Q1 has closed, but i still have backlog with scheduled dates in Q1 (known as past due), but i dont want these numbers in my Q1 view, they should fall into the current quarter past due column. But i'm not sure how to get there.. 

I really appreciate the help, and if you would like more details, i can provide.  thank you again.

Josh

Here is the end game:  note the backlog and past due for 2020-Q1 should be 0. 

Scorecard

This is the AOP table,  the yellow columns are vlookups to the dimension tables.

AOP

This is the backlog table, same thing in the far right are lookups, but there is also the "Prior Quarter End Date" which then triggers the "Past Due" calc. 

Shipments

this is the shipments report

Backlog

Two dimension tables:  customer and fiscal date. 

Customer DImensionDate Dimension

[v-juanli-msft]

Hi @Anonymous 

"unified customer" is from "another table", "backlog" is from table "sales", "item number" is from "backlog",

If my understanding is correct, please show the relationships among them.

Best Regards

Maggie

[amitchandak]

@Anonymous 

The next level, what is it. item or something else joins seems to be wrong. Or you are taking it from sales.

Can you share the relationship diagram? Can you share sample data and sample output.