Forum Discussion

rakunn's avatar
rakunn
Frequent Visitor
9 years ago
Solved

Calculating rows in Power Bi

Hello everyone,

 

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

 

Level 0Level 1Value
RevenuesSales100,00
Operating expensesOutsourcing30,00
Operating expensesSalaries50,00
Operating expensesOther10,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 LabelsSum of Value
1. Revenues 
Sales100
2. Operating expenses 
Other10
Outsourcing30
Salaries50
3. Revenues - Operating expenses10
  

 

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ł

 

  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    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's avatar
      rakunn
      Frequent Visitor
      9 years ago

      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ł

  • sekarsdream's avatar
    sekarsdream
    New Member
    9 years ago

    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.