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.

Anonymous's avatar
Anonymous
Not applicable
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.