Forum Discussion
Data Model: creating a P&L per customer
Hi Everyone,
I would like to create a P&L per customer in Power Pivot. Then I want to have this P&L via a pivot table with a slicer on the customer type to understand the profitability by customer type. Let me explain myself.
I have some dimension tables such as Period, Cluster (which is the customer), division (location of the customer), debtor type (big customer, medium, small).
The first data table is from one source which gives the revenue and margin per customer name. I would like my P&L to take the revenue from this table.
I will add a final data table which gives the P&L data (revenue, margin, costs etc.). We don't have any vision of customers.
So I won't to create the following P&L
Revenue > from the Fact_Revenue table because I have the revenue per customer
Margin > from the Fact_Revenue table because I have the margin per customer
Expenses > from the Fact_P&L table I will add.
Thanks,
Hi, Tarek78210
Based on your information, I create sample tables:
Then create measures:
Total Margin = SUM('Fact_Revenue'[Margin])Total Revenue = SUM('Fact_Revenue'[Revenue])Total Expenses = VAR TotalRevenueAll = CALCULATE(SUM(Fact_Revenue[Revenue]), ALL(Fact_Revenue)) VAR TotalExpensesAll = 21000 -- Let's assume that the total cost is:21000 RETURN SUM(Fact_Revenue[Revenue]) / TotalRevenueAll * TotalExpensesAllPut them in table view, and put DebtorCategory in slicer. Here is my preview:
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Tarek78210 ,
To prepare a P&L by customer, you can use the Fact_Revenue table for both revenue and cost of goods sold (CoGS). However, for expenses, an allocation method will be required since these are typically fixed costs that cannot be directly attributed to specific customers. For example, your sales departments or sales representatives may serve only certain customers, so their costs should be allocated within the group of customers they manage. In contrast, back-office costs, which are not tied to any specific customers, are generally allocated evenly across all customers.
If you would like more details, I’d be happy to assist further.
Best regards,
Hi, Tarek78210
Based on your information, I create sample tables:
Then create measures:
Total Margin = SUM('Fact_Revenue'[Margin])Total Revenue = SUM('Fact_Revenue'[Revenue])Total Expenses = VAR TotalRevenueAll = CALCULATE(SUM(Fact_Revenue[Revenue]), ALL(Fact_Revenue)) VAR TotalExpensesAll = 21000 -- Let's assume that the total cost is:21000 RETURN SUM(Fact_Revenue[Revenue]) / TotalRevenueAll * TotalExpensesAllPut them in table view, and put DebtorCategory in slicer. Here is my preview:
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
