Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
ahamdan
Frequent Visitor

Calculate Gross margin in Profit and Loss statement

Dear Expert,

 

I need to do following in Power BI, I need to have the calculation as below, any one can help.

 

 Total
Revenue100
Cost90
Gross Profit10
Gross Margin10%

 

Equation as below: 

 

Gross Profi = Revenue - Cost

Gross Margin = (Gross Profit / Revenue ) * 100

 

Regards,

 

1 ACCEPTED SOLUTION

Hi @ahamdan,

 

=> Simply, I am trying to do P&L and I need it to be in the same format as I mentioned.

 

So did you mean that you want to make the Gross Profi and Gross Margin also in the same column with Cost and Revenue? If so, I'm afraid it cannot be achieved in Power BI.

 

You should know that Power BI is a data analytics tool. We can create a new calculated column or a measure to calculate value based on the source table. However it is not possible to insert the calculated value into the source table. This should be done at data source side.

 

Thanks,
Xi Jin.

 

View solution in original post

7 REPLIES 7
dineshksh
New Member

Hello All,

 

Kindly can you help to create simple profit and loss table in power bi by row wise and data is exported from excel as below,

 

Accounts        Amounts

 

Cost                 50000

Revenue        100000

Gross Profit     50000

% Profit               500

Link https://community.powerbi.com/t5/Desktop/Profit-Loss-on-Matrix-Table-in-power-BI/td-p/508365.

 

Example

A_SwitchedMeasures_GroupPKR = sumx(ListMeasure_Group,
SWITCH([Selected_Measure_Group],
1,[A_Tot_Rev_PKR],
2,[A_COR_PKR],
3,[Margin_A_PKR],
4,Divide([Margin_A_PKR],[A_Tot_Rev_PKR])*100,
5,[A_Practice_OH_PKR],
6,([Margin_A_PKR]-[A_Practice_OH_PKR]),
7,[A_Sell_PKR],
8,[CM_A_PKR]-[A_Practice_OH_PKR],
9,Divide([CM_A_PKR]-[A_Practice_OH_PKR],[A_Tot_Rev_PKR])*100,
10,[A_AdminExpenses_PKR],
11,[A_OIncome_PKR],
12,[A_Donation_Group_PKR],
13,([A_Net Profit_PKR]+[A_OIncome_PKR]-[A_Donation_Group_PKR]),
14,Divide(([A_Net Profit_PKR]+[A_OIncome_PKR]-[A_Donation_Group_PKR]),[A_Tot_Rev_PKR])*100
)
)
Ghulam Abbas
03065297514

sorry i need in column wise pls

v-xjiin-msft
Solution Sage
Solution Sage

Hi @ahamdan,

 

Based on your sample data, the Revenue and Cost are in one same column. So is it true as they are truly in one same column? 

 

If so, the formula for Gross Profi can be like this:

 

Gross Profi =
CALCULATE ( MAX ( Test[Value] ), Test[Column] = "Revenue" )
    - CALCULATE ( MAX ( Test[Value] ), Test[Column] = "Cost" )

And Gross Margin can be:

 

Gross Margin =
DIVIDE (
    [Gross Profi],
    CALCULATE ( MAX ( Test[Value] ), Test[Column] = "Revenue" )
)

If the Revenue and Cost are came from other measures. Please don't be lazy to share use your acutal situation. Since we can't see your real data, it'll help us a lot to understand your requirement if you can share us your actual sample data and the measure formulas.

 

Thanks,
Xi Jin.

Thank you Sir, 

 

Yes Cost and Revenue are in the same column.

Simply, I am trying to do P&L and I need it to be in the same format as I mentioned.

 

 

Hi @ahamdan,

 

=> Simply, I am trying to do P&L and I need it to be in the same format as I mentioned.

 

So did you mean that you want to make the Gross Profi and Gross Margin also in the same column with Cost and Revenue? If so, I'm afraid it cannot be achieved in Power BI.

 

You should know that Power BI is a data analytics tool. We can create a new calculated column or a measure to calculate value based on the source table. However it is not possible to insert the calculated value into the source table. This should be done at data source side.

 

Thanks,
Xi Jin.

 

Thanks for your reply.

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.