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.
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.
Here is the end game: note the backlog and past due for 2020-Q1 should be 0.
This is the AOP table, the yellow columns are vlookups to the dimension tables.
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.