Comparing sales for customer and sales rep / Comparing one value by two dimensions

[djaaiin93]

Hello,

I have a rather peculiar report that I'm stuck on. As you can see, I'm not even sure how to title this. This is what my sales fact table looks like. I already have dim tables for customers and sales reps with the relationships set up and working fine. 

Customer	Sales Rep	Sale Date	Sales Amount
A	X	1 Jan 2020	500
B	XX	1 Jan 2020	600
C	Y	1 Jan 2020	200
D	YY	1 Jan 2020	300

Sales Reps X and XX sell products to customers A and B. Sales Rep Y collects stock from A and sells to customer C; Sales Rep YY collects stock from B and sells to D. 

I would like to compare customers A and B purchases with sales of reps Y and YY. 

Customer A	500	Sales Rep Y	200	200/500 = 40%
Customer B	600	Sales Rep YY	300	300/600 = 50%

How would I do this? Would I need to create additional tables to what I already have? I'm also not sure what visual(s) would be a good fit for this situation.

Thank you.

[v-rzhou-msft · Accepted solution]

Hi @djaaiin93 

I think you need to build a relationship table to show where the stock from and where the stock to go. This is important, we need a table with this information to let Power BI konw when we calculate the percentage.

I think you can try to add Sales ID in your Sales Fact table and build a New table by Sales ID.

Sales Table:

Relationship Table:

Relate two tables by SalesID (Sale Table) and From Sales ID(Relationship Table)

Build measures:

Sale stock amount = 
CALCULATE(SUM(Sales[Sales Amount]),FILTER(ALL(Sales),Sales[SalesID] = MAX(Relationship[To Sales ID])))

Percentage = 
DIVIDE([Sale stock amount],SUM(Sales[Sales Amount]))

Result:

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.