Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How can I make calculations in a visual table for Income Statement/ P&L

Hi All,   This is a visusal I have creating using GL and Account Tables Kindly let me know how can I make the calculations for missing rows.   Gross profit = Revenue - Cost of Sale GP Margin = ...
powerbi
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    Please share your sample pbix file's link, and then I can try to look into it.

    Thanks.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    I am a bit confused about the logic, but please check the below picture and the attached pbix file.

    I hope you can try to amend the formula if I misunderstood the logic.

     

    expected result measure: =
VAR _revenue =
    CALCULATE (
        [Total Actuals],
        'Account Mapping'[Row Index] = 1,
        REMOVEFILTERS ( IsStructure )
    )
VAR _cost =
    CALCULATE (
        [Total Actuals],
        'Account Mapping'[Row Index] = 2,
        REMOVEFILTERS ( IsStructure )
    )
VAR _grossprofit = _revenue + _cost
VAR _gpmargin =
    DIVIDE ( _grossprofit, _revenue )
VAR _otherincome =
    CALCULATE (
        [Total Actuals],
        'Account Mapping'[Row Index] = 6,
        REMOVEFILTERS ( IsStructure )
    )
VAR _sga =
    CALCULATE (
        [Total Actuals],
        'Account Mapping'[Row Index] = 5,
        REMOVEFILTERS ( IsStructure )
    )
VAR _ebitda = _grossprofit + _otherincome - _sga
RETURN
    SWITCH (
        SELECTEDVALUE ( IsStructure[Row Index] ),
        3, _grossprofit,
        4, FORMAT ( _gpmargin, "#.00%" ),
        7, _ebitda,
        [Total Actuals]
    )
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
2 years ago

Hi,

I am a bit confused about the logic, but please check the below picture and the attached pbix file.

I hope you can try to amend the formula if I misunderstood the logic.

 

expected result measure: =
VAR _revenue =
    CALCULATE (
        [Total Actuals],
        'Account Mapping'[Row Index] = 1,
        REMOVEFILTERS ( IsStructure )
    )
VAR _cost =
    CALCULATE (
        [Total Actuals],
        'Account Mapping'[Row Index] = 2,
        REMOVEFILTERS ( IsStructure )
    )
VAR _grossprofit = _revenue + _cost
VAR _gpmargin =
    DIVIDE ( _grossprofit, _revenue )
VAR _otherincome =
    CALCULATE (
        [Total Actuals],
        'Account Mapping'[Row Index] = 6,
        REMOVEFILTERS ( IsStructure )
    )
VAR _sga =
    CALCULATE (
        [Total Actuals],
        'Account Mapping'[Row Index] = 5,
        REMOVEFILTERS ( IsStructure )
    )
VAR _ebitda = _grossprofit + _otherincome - _sga
RETURN
    SWITCH (
        SELECTEDVALUE ( IsStructure[Row Index] ),
        3, _grossprofit,
        4, FORMAT ( _gpmargin, "#.00%" ),
        7, _ebitda,
        [Total Actuals]
    )
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Jihwan,

 

It is working as expected (I have edit the code for NPBT and NPAT).

 

Thank you a lot for the quick support!!

Quick question: How can highlight only the EBITDA and EBITDA Margin row. (Tried with formating but didnt work)

 

Many Thanks

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi, One of ways to achieve this is to create a measure like below, and then put into the background color option, something like below.

    Please check the below picture and the attached pbix file.

     

     

     

    background color measure: = 
IF( SELECTEDVALUE( IsStructure[Row Index] ) = 7, "Yellow" )

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Jihwan,

       

      Thank you a lot for the suggestion.

       

      I tried this before. However I want to highlight both EBITDA and EBITDA Margin entire row at once. Kindly let me know how to imrove this method to cheive this

       

       

       

      Thanks