Forum Discussion
Comparing sales for customer and sales rep / Comparing one value by two dimensions
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.
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.
1 Reply
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.