Hi Yen,

You can add a new measure in Power Pivot, not in Power Query. I don't know the math behind your data, what formula are you looking for?

Hi, 

I would like to use the data to create dashboard in Power BI or Excel Dashboard.

Could you please teach me how to add a new measure?

E.g.

I only have information for sales, cogs, expenses.

I would like to new measure (a) Gross Profit = Sales - COGS (b) Operating Profit = Gross Profit - Expenses

However, it is not easy to do in the data i am having now.

You are right that I'm currently using Power Pivot to do so, but I need the data for Power BI or Excel Dashboard.  I do not know how to switch between the data.

Thank you.

Attached an example.

You should learn at least the basics of Power Pivot before creating reports, here is a video that can get you started:

https://www.youtube.com/watch?v=TK0CJBaqvnY

Hi Yen,

Creating a Profit and Loss statement in Power BI/Power Pivot is very difficult because it's not designed to work on rows within columns. I recommend you search for some videos/blog posts on "Power BI Profit & Loss" and try to get some pointers.

Good luck!

Mynda

Thank you very much

Hi Catalin and Mynda,

Thanks for the information.  Actually, i am not looking into building Power BI P&L.  Due to lacking of information (i.e. GP), I have the difficulty to do charts/graph for GP and GP% analysis.  My purpose is create graph and charts for GP and GP%, hence i need to know how to write the formula in Power BI.

Could you please help me?  Thank you very much.

Hi Yen,

It will depend on the structure of your model, but generally speaking, it would be like this:

GrossMargin = Sales[SalesAmount] - Sales[TotalProductCost]

Gross Margin % = DIVIDE ( SUM ( Sales[GrossMargin] ), SUM (Sales[SalesAmount] ) )

Mynda


Thank you.