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

Question

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.&nbsp;CustomerSales RepSale DateSales AmountAX1 Jan 2020500BXX1 Jan 2020600CY1 Jan 2020200DYY1 Jan 2020300&nbsp;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.&nbsp;&nbsp;I would like to compare customers A and B purchases with sales of reps Y and YY.&nbsp;&nbsp;Customer A500Sales Rep Y200200/500 = 40%Customer B600Sales Rep YY300300/600 = 50%&nbsp;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.&nbsp;Thank you.

anonymous · Accepted Answer

Hi&nbsp;djaaiin93&nbsp;
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
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.&nbsp;

Forum Discussion

Related content

Table Sort , sort order , comparer

Comparing two Semantic Models

Comparing activity performance

Compare 2 Tables

Compare value in dimension table