Forum Discussion
Profit & Loss Table
Hi,
I'm relatively new to Power BI, I'm trying to create a small profit and loss report but I'm quite stuck.
I've got a few tables such as GL entries, profit & loss structure, GL mapping & date, which I believe are all mapped and related properly.
Below is how I want it to be produced but I can't figure out how to populate the aggregated rows such as Gross Profit, Gross Profit Margin %, Income Before Overheads, Profit & Loss.. I can create them as measures no problem which can then displayed in visuals separately but I'd like them to be in the Ac column instead.
The Ac column is a Financial Value measure
Any help would be greatly appreciated.
Thanks!
Hi Anonymous ,
I created some data:
You can use Measure to calculate all the metrics, using the Switch() function when creating a column with the metrics name
Here are the steps you can follow：
1. Create measure.
Cost of sales = SUMX(ALL('Table'),[Cost])Gross Profit = var _total= SUMX(ALL('Table'),[Prcice]) var _Cost= SUMX(ALL('Table'),'Table'[Cost]) return _total - _CostGross Profit Margin % = var _total= SUMX(ALL('Table'),[Prcice]) return DIVIDE( [Gross Profit],_total)Other Income Measure = SUMX(ALL('Table'),[Other Income])Income Before Overheads Measure = SUMX(ALL('Table'),[Income Before Overheads])Overheads measure = SUMX(ALL('Table'),[Overheads])2. Create a table using Enter data.
Create measure:
AC = SWITCH( TRUE(), MAX('Group_Table'[Group])="Gross Profit",[Gross Profit], MAX('Group_Table'[Group])="Cost of sales",[Cost of sales], MAX('Group_Table'[Group])="Gross Profit Margin %",[Gross Profit Margin %], MAX('Group_Table'[Group])="Other Income",[Other Income Measure], MAX('Group_Table'[Group])="Income Before Overheads",[Income Before Overheads Measure], MAX('Group_Table'[Group])="Overheads",[Overheads measure])3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
Hi Anonymous ,
I created some data:
You can use Measure to calculate all the metrics, using the Switch() function when creating a column with the metrics name
Here are the steps you can follow：
1. Create measure.
Cost of sales = SUMX(ALL('Table'),[Cost])Gross Profit = var _total= SUMX(ALL('Table'),[Prcice]) var _Cost= SUMX(ALL('Table'),'Table'[Cost]) return _total - _CostGross Profit Margin % = var _total= SUMX(ALL('Table'),[Prcice]) return DIVIDE( [Gross Profit],_total)Other Income Measure = SUMX(ALL('Table'),[Other Income])Income Before Overheads Measure = SUMX(ALL('Table'),[Income Before Overheads])Overheads measure = SUMX(ALL('Table'),[Overheads])2. Create a table using Enter data.
Create measure:
AC = SWITCH( TRUE(), MAX('Group_Table'[Group])="Gross Profit",[Gross Profit], MAX('Group_Table'[Group])="Cost of sales",[Cost of sales], MAX('Group_Table'[Group])="Gross Profit Margin %",[Gross Profit Margin %], MAX('Group_Table'[Group])="Other Income",[Other Income Measure], MAX('Group_Table'[Group])="Income Before Overheads",[Income Before Overheads Measure], MAX('Group_Table'[Group])="Overheads",[Overheads measure])3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Creating a Profit and Loss dashboard in Power BI can be harder than it looks, especially when you want totals and margin rows to appear in the same report structure.
If you're still early in the process, you might want to look at some ready-made P&L dashboard templates. They already have the structure set up and can save quite a bit of trial and error.
There are some examples here: https://vidi-corp.com/profit-and-loss-dashboards/
