Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Subtotal within P&L

Hello,

 

Please can I get some help in getting a subtotal for 'Total Cost of Sales' to appear in the matrix table below?  

 

Total revenue and total units traded are currently data values within the dataset and hence I am not having the same issue with these.  

 

I have also provided my attempts at using a calculated metric and using the Switch function (to no avail).

 

Attempt - 

 

Subtotal required

 

Data tables

Many thanks,

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Anonymous 

    Please try formula as below:

    Output = 
VAR _Total_cost_of_sales =
    CALCULATE (
        SUM ( 'Project Level Data'[Value] ),
        FILTER (
            ALL ( 'P&L Presentation' ),
            'P&L Presentation'[Profit and loss itesms]
                IN { "Land Traded", "WIP Traded", "Provisions", "Abort Costs" }
        )
    )
VAR _Total_revenue =
    CALCULATE (
        SUM ( 'Project Level Data'[Value] ),
        FILTER (
            ALL ( 'P&L Presentation' ),
            'P&L Presentation'[Profit and loss itesms] = "Total Revenue"
        )
    )
VAR _Total_Trading_Profit = _Total_cost_of_sales + _Total_revenue
VAR _Trading_Profit_percent = _Total_Trading_Profit / _Total_revenue
RETURN
    SWITCH (
        SELECTEDVALUE ( 'P&L Presentation'[Profit and loss itesms] ),
        "Total Cost of Sales", _Total_cost_of_sales,
        "Total Trading Profit", _Total_Trading_Profit,
        "Trading Profit%", FORMAT ( _Trading_Profit_percent, "Percent" ),
        SUM ( 'Project Level Data'[Value] )
    )

    If it doesn't work, please share your sample file for further research.

    Best Regards,
    Community Support Team _ Eason

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Please see below

       

      Sample data and Desired Output

       

      Private Units                                       30
      BTR Units                                       10
      Affordable Units                                       30
      Total Units Traded                                       70
      * 
      Private Revenue                                 8,000
      BTR Revenue                                        -  
      Affordable Revenue                                 2,000
      Commercial Revenue                                       80
      Other Revenue                                        -  
      Total Revenue                               10,080
      **                                        -  
      Land Traded-                                1,000
      WIP Traded-                                2,000
      Provisions-                                   500
      Abort Costs-                                   500
      Total Cost of Sales-                                4,000
      *** 
      Total Trading Profit                                 6,080
      Trading Profit %60%

       

      Project IDMonthActual/ForecastNameAttributeValue
      PBJan-22ForecastPotters AvenueLand Cost-10
      PBJan-22ForecastPotters AvenuePrivate Units Sold20
      PBJan-22ForecastPotters AvenuePrivate Units UnSold10
      PBJan-22ForecastPotters AvenueSocial Units Sold20
      PBJan-22ForecastPotters AvenueSocial Units Unsold10
      PBJan-22ForecastPotters AvenueMgmt Provision - Affordable Units Adjustment0
      PBJan-22ForecastPotters AvenuePRS Units Sold5
      PBJan-22ForecastPotters AvenuePRS Units Unsold5
      PBJan-22ForecastPotters AvenueUnits70
      PBJan-22ForecastPotters AvenueGross Private Revenue10000
      PBJan-22ForecastPotters AvenueLess Private Incentives-2000
      PBJan-22ForecastPotters AvenueGross Social Revenue2000
      PBJan-22ForecastPotters AvenueLess Social Incentives0
      PBJan-22ForecastPotters AvenueMgmt Provision - Affordable Revenue Adjustment0
      PBJan-22ForecastPotters AvenueGross Commercial Revenue100
      PBJan-22ForecastPotters AvenueLess Commercial Incentives-20
      PBJan-22ForecastPotters AvenueFreehold Revenue0
      PBJan-22ForecastPotters AvenueOther Revenue0
      PBJan-22ForecastPotters AvenuePX Revenue0
      PBJan-22ForecastPotters AvenueRevenue10080
      PBJan-22ForecastPotters AvenueLand Released-1000
      PBJan-22ForecastPotters AvenueWIP Released-2000
      PBJan-22ForecastPotters AvenueCompleted Unit Provisions0
      PBJan-22ForecastPotters AvenueMgmt Provision - Cost of Sales Adjustment-500
      PBJan-22ForecastPotters AvenueAbort Costs-500
      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        4 years ago

        Hi, Anonymous 

        Please try formula as below:

        Output = 
VAR _Total_cost_of_sales =
    CALCULATE (
        SUM ( 'Project Level Data'[Value] ),
        FILTER (
            ALL ( 'P&L Presentation' ),
            'P&L Presentation'[Profit and loss itesms]
                IN { "Land Traded", "WIP Traded", "Provisions", "Abort Costs" }
        )
    )
VAR _Total_revenue =
    CALCULATE (
        SUM ( 'Project Level Data'[Value] ),
        FILTER (
            ALL ( 'P&L Presentation' ),
            'P&L Presentation'[Profit and loss itesms] = "Total Revenue"
        )
    )
VAR _Total_Trading_Profit = _Total_cost_of_sales + _Total_revenue
VAR _Trading_Profit_percent = _Total_Trading_Profit / _Total_revenue
RETURN
    SWITCH (
        SELECTEDVALUE ( 'P&L Presentation'[Profit and loss itesms] ),
        "Total Cost of Sales", _Total_cost_of_sales,
        "Total Trading Profit", _Total_Trading_Profit,
        "Trading Profit%", FORMAT ( _Trading_Profit_percent, "Percent" ),
        SUM ( 'Project Level Data'[Value] )
    )

        If it doesn't work, please share your sample file for further research.

        Best Regards,
        Community Support Team _ Eason