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

[Anonymous]

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 = Gross Profit/Revenue

EBITDA =  Gross Profit+ Other Income - SQ&A

Thanks in advance

[Jihwan_Kim · Accepted solution]

Hi,

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

Thanks.

[Anonymous]

Thank you a lot for the support!

[Anonymous]

Hi Jihwan,

Resharing the sample file link. Hope this is accessible.

https://drive.google.com/file/d/1DVEE_B33t-JY8SStyX7QTYOP_ZjeC1oW/view?usp=sharing

Thanks

[Jihwan_Kim · Accepted solution]

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]

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]

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]

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

[Anonymous]

HI Jihwan,

Please find the sample file attached. 

Sample File - Power BI

Many thanks

[Jihwan_Kim]

Hi,

I think the link directs to the power bi service, not to the file.

[Jihwan_Kim]

Hi, I am not sure how your datamodel looks like, but please try to write an additinoal measure something like below whether it suits your requirement.

SELECTEDVALUE function - DAX | Microsoft Learn

SWITCH function (DAX) - DAX | Microsoft Learn

expected result measure: =
SWITCH (
    SELECTEDVALUE ( tablename[Description Profit & Loss Statement] ),
    "Gross profit",
        CALCULATE (
            [Total Actuals],
            tablename[Description Profit & Loss Statement] = "Revenue"
        )
            - CALCULATE (
                [Total Actuals],
                tablename[Description Profit & Loss Statement] = "Cost of Sale"
            ),
    "GP Margin",
        DIVIDE (
            CALCULATE (
                [Total Actuals],
                tablename[Description Profit & Loss Statement] = "Gross Profit"
            ),
            CALCULATE (
                [Total Actuals],
                tablename[Description Profit & Loss Statement] = "Revenue"
            )
        ),
    "EBITDA",
        CALCULATE (
            [Total Actuals],
            tablename[Description Profit & Loss Statement] = "Gross Profit"
        )
            + CALCULATE (
                [Total Actuals],
                tablename[Description Profit & Loss Statement] = "Other Income"
            )
            - CALCULATE (
                [Total Actuals],
                tablename[Description Profit & Loss Statement] = "SQ&A"
            ),
    [Total Actuals]
)

[Anonymous]

Hi Jihwa,

Thank you for the quick answer.

I tried the Switch function, However I'm not getting the results.  Kindly advise

[Jihwan_Kim · Accepted solution]

Hi,

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

Thanks.