topic Re: How can I make calculations in a visual table for Income Statement/ P&L in DAX Commands and Tips

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

Anonymous — Tue, 14 Nov 2023 05:46:10 GMT

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

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

Jihwan_Kim — Tue, 14 Nov 2023 06:23:46 GMT

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] )

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

Anonymous — Tue, 14 Nov 2023 06:53:28 GMT

Hi Jihwa,

Thank you for the quick answer.

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

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

Jihwan_Kim — Tue, 14 Nov 2023 17:24:15 GMT

Hi,

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

Thanks.

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

Anonymous — Tue, 14 Nov 2023 18:04:01 GMT

HI Jihwan,

Please find the sample file attached.

Sample File - Power BI

Many thanks

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

Jihwan_Kim — Wed, 15 Nov 2023 03:31:48 GMT

Hi,

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

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

Anonymous — Wed, 15 Nov 2023 03:45:48 GMT

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

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

Jihwan_Kim — Wed, 15 Nov 2023 04:20:27 GMT

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] )

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

Anonymous — Wed, 15 Nov 2023 08:07:06 GMT

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

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

Jihwan_Kim — Wed, 15 Nov 2023 17:38:09 GMT

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" )

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

Anonymous — Wed, 15 Nov 2023 17:53:38 GMT

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

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

Anonymous — Sun, 19 Nov 2023 11:38:57 GMT

Thank you a lot for the support!