Accumulating subtotals

[guyclewlowi2]

Is it possible to add accumulating sub totals into a matrix table in Power BI. So for example Income, cost of sales and then a subtotal called gross profit, then overheads and another subtotal called net profit. The net profit would be a calculation of gross profit + overheads and not just total the overheads

[Ashish_Mathur]

Hi,

This can be done in Excel using CUBE functions.

[KHorseman]

These each would be written as separate measures(i.e. [Income], [Cost of Sales], and [Gross Profit]), then in your matrix you would use the new "show measures on rows" formatting feature to get the layout you're describing.

[Anonymous]

Hi @guyclewlowi2,

For your scenario, I think you can keep the original table structure to build a sample file with some fake data , then share it here.

Regards,

Xiaoxin Sheng

[guyclewlowi2]

Please see below a link to the power BI example table and the example data

Sample BI table

Sample transactions

[Anonymous · Accepted solution]

Hi @guyclewlowi2,

>>So for example Income, cost of sales and then a subtotal called gross profit, then overheads and another subtotal called net profit. The net profit would be a calculation of gross profit + overheads and not just total the overheads

Power bi not support these feature, you should write measures to calculate for these calculations and drag them to values field.

Regards,

Xiaoxin Sheng

[Anonymous]

Can you provide some sample data so that we can have better understanding towards result formulation?

Regards,

Hemant

[guyclewlowi2]

Hi Hemant,

Thanks for replying to the post, because its company financial data I cant share but it is typical nominal ledger transactions (in this case from SAGE 50)

[Anonymous]

You can obviously mask the data & provide us for our understanding towards its structure & the result you want to replicate out of it.

Regards

[KHorseman]

Or use https://www.mockaroo.com/ to make some fake sample data that has the same structure.