Forum Discussion

RossBateman's avatar
RossBateman
Helper I
6 years ago
Solved

Creating custom subtotals within a matrix table

Hello I'm trying to create a profit and loss account using a matrix table. The data is mapped out into 2 levels. The highest level categories are as follows; Net Sales, CLOGS, Marketing, Payroll, Ov...
Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi RossBateman ,

     

    The NetProfit is incorrect from your picture, then you may create the "%Actually Budget" like DAX below to get correct subtotal.

     

     

    %Actually Budget=

var d= IF(Table1[Budget]<>BLANK(),DIVIDE(Table1[Actual]-Table1[Budget], Table1[Budget],0),0)

var _table = SUMMARIZE(Table1, Table1[P&L Summary],"_Value", d)

return

IF(HASONEVALUE(Table1[P&L Summary]), d, SUMX(_table,[_Value]))

     

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Aryna's avatar
Aryna
Post Partisan
5 months ago

A common approach is to use your mapping table to control the structure and then define measures for things like Gross Profit, which aggregate across categories. You may also need a disconnected table or a custom sort order to get the layout exactly how you want.

 

Alternatively, you can check some pre-built Profit & Loss Power BI templates available here: https://vidi-corp.com/profit-and-loss-dashboards/

 

Sometimes it’s easier to start from an existing structure and adapt it rather than building everything from scratch.