Data Model: creating a P&L per customer

[Tarek78210]

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,

[Anonymous · Accepted solution]

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 * TotalExpensesAll

Put 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.

[DataNinja777]

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,