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

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , First two screenshot are not clear.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Also refer, why Grand total is wrong

https://www.youtube.com/watch?v=ufHOOLdi_jk

  • 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