Add new calculated rows in matrix

[eduardosilvin3]

Hello all, I have this calc table, comes from a table where I have in the same rows "sales" and "costs".... so this was to get all the rows and add it in the same column, now I have all as I need in the matrix as a financial statemant but I need to add more rows with new calculations, like "item net value - item_Total cost"

Any ideas?

[v-jingzhan-msft]

Hi @eduardosilvin3 

Since you need to add more rows with new calculations in the matrix, you may need to make those calculations in advance and union the results to your current calculated values. Then use data from the new table into the matrix. An example: 

new table =
VAR _table1 =
    SUMMARIZE (
        'DB Sales',
        'DB Sales'[Index],
        'DB Sales'[invoice_no],
        'DB Sales'[invoice_date],
        "new column 1", SUM ( 'DB Sales'[item_net_value] ),
        "new column 2", SUM ( 'DB Sales'[Sub Revenue] )
    )
VAR _table2 =
    SUMMARIZE (
        'DB Sales',
        'DB Sales'[Index],
        'DB Sales'[invoice_no],
        'DB Sales'[invoice_date],
        "new column 1", SUM ( 'DB Sales'[item_TotalCost] ),
        "new column 2", SUM ( 'DB Sales'[Sub Cost] )
    )
VAR _table3 =
    SUMMARIZE (
        'DB Sales',
        'DB Sales'[Index],
        'DB Sales'[invoice_no],
        'DB Sales'[invoice_date],
        "new column 1", SUM ( 'DB Sales'[item_net_value] ) - SUM ( 'DB Sales'[item_TotalCost] ),
        "new column 2", ..........
    )
RETURN
    UNION ( _table1, _table2, _table3 )

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[eduardosilvin3]

Thank you, did not what I Expected but helped me with some ideas

[Ahmedx]

Refer to this solution

https://community.fabric.microsoft.com/t5/Desktop/Matrix-Table-Custom-Row-Total/m-p/3467966#M1148830

[eduardosilvin3]

thanks for the answer, but I did not get how to replicate your file into my file

[Ahmedx]

Can you please share your demo input and expected output!

[eduardosilvin3]

Thanks, here are the files. I created a non sensitive data file, it really comes from SQL Server, but I replace everything from the excel. So I have 4 tables, 2 from the DB (the DB and some Power Query codes to get the structure I need to show in the matrix). The 3rd table is to separate in one whole table by costs and revenues, because they are in the same rows. And the 4th is for dates.

So now I must add contribution, contribution %, EBITDA, etc (the rows I added in power query to get the structur). All those calculations comes from the DB table, but now I am lost how add those calculations in the matrix as extra rows, following the 'DB Structure' table, not only as a measure appart and have the possibility to filter by all the other columns in the original DB. I have all the calculations but I dont know how to add it to the matrix.

Hope you can have any idea that can help.

Regards!

PBI Help