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 0 Level 1 Value Revenues Sales ...
  • 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

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ł