Calculating rows in Power Bi

[rakunn]

Hello everyone,

I am building financial report and I need to calculate Profit minus Operating Revenues row. Lets say, my table looks following:

Level 0	Level 1	Value
Revenues	Sales	100,00
Operating expenses	Outsourcing	30,00
Operating expenses	Salaries	50,00
Operating expenses	Other	10,00

My question is, can I put in the Matrix table row called Revenues minus Operating expenses, which will calculate the result?

In excel and pivot tables, I am able to do that with option "Calculated item", and I would like to do the same in Power BI, as below:

Row Labels	Sum of Value
1. Revenues
Sales	100
2. Operating expenses
Other	10
Outsourcing	30
Salaries	50
3. Revenues - Operating expenses	10

All I can add is new column and I do not see option like "add calculated row". If you need more details please tell.

Best wishes,

Rafał

[sekarsdream]

Hello Everyone i have similar kind of issue, i need to calulate the percentage of the indiidual values with the total for each column for different month as shown in the figure.

[v-yulgu-msft · Accepted solution]

Hi, @rakunn

Please follow the screenshots to work around your issue.

Reference to DAX formulas:

Table1 =
SUMMARIZE (
    'financial report',
    'financial report'[Level0],
    'financial report'[Level1],
    "SumOfValue", SUM ( 'financial report'[Value] )
)

Table4 =
SUMMARIZE (
    'financial report',
    'financial report'[Level0],
    "RowLebels", "Revenues - Operating expenses",
    "Level1", BLANK (),
    "SumOfValue", LASTNONBLANK ( Table1[SumOfValue], SUM ( 'financial report'[Value] ) )
        - SUM ( 'financial report'[Value] )
)

Table 5 =
SUMMARIZE (
    SELECTCOLUMNS (
        Table4,
        "RowLebels", Table4[RowLebels],
        "Level1", BLANK (),
        "SumOfValue", Table4[SumOfValue]
    ),
    [RowLebels],
    "Level1", BLANK (),
    "SumOfValue", SUM ( Table4[SumOfValue] )
)

Table6 = UNION(Table1,'Table 5')

If you have any question, please feel free to ask.

Best regards,
Yuliana Gu

[rakunn]

Hello Yuliana,

Many thanks for your time and effort put in your post.

DAX is truly impressive, but may be a little bit of intimidating for less experienced users (like me). Have you heard about some good online courses about this language? Or powerbi.com resources will suffice?

Hope to hear from you soon,

Rafał

[v-yulgu-msft]

Hi @rakunn

Microsoft provides many documents written about the snytax and usage of DAX function:

Data Analysis Expressions (DAX) Reference

Thanks,
Yuliana Gu