Forum Discussion

Tarek78210's avatar
Tarek78210
Frequent Visitor
2 years ago
Solved

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 custome...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
DataNinja777
Icon for Super User rankSuper User
2 years ago

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,