cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Help with matrix for 3 tables

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.  Capture.PNG

3 REPLIES 3
Highlighted
Super User IV
Super User IV

@bearpoz79 

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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Hi @bearpoz79 

"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

 

 

Highlighted
Frequent Visitor

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. 

ScorecardScorecard

 

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

AOPAOP

 

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. 

 

ShipmentsShipments

 

this is the shipments report

BacklogBacklog

 

Two dimension tables:  customer and fiscal date. 

 

Customer DImensionCustomer DImensionDate DimensionDate Dimension

 

 

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors