Simplify P&L with Calculated Rows

[altesv]

Hi have a P&L with calculated rows within the matrix visual. It is a complicated process because PBI doesn't seem to support this.

Step 1 for me was to duplicate all of my data that I would be creating a calculated row for (literally copy paste margin and profit data so that it is duplicated in my source). 

Step 2 was to create a percentage calculation measure where I divided each row by the value in revenue.

Step 3 was to create a logical calculation measure which retrieved from either the "data" column or the "percent calc" column. Plus rules for formatting percents.

	Data	Percent Calc	Logical column (final result)
Revenue	1000	1	1000
Expenses A	300	0.3	300
Margin	700	0.7	700
Margin %	700	0.7	70%
Expenses B	250	0.25	250
Profit	450	0.45	450
Profit %	450	0.45	45%

Does anyone know of a simpler way to do this while retaining this P&L structure? Particularly, is there a way that I could have the duplicate data appear without me duplicating in the data source?

[v-yuezhe-msft]

@altesv,

What is the original format of your table? Could you please post the table structure here?

Regards,
Lydia

[altesv]

It's pretty simple. Data source is month end balances across the following intersections:

Year | Month | Account | Business Segment | Business Region | Amount 

[v-yuezhe-msft]

@altesv,

What logic do you use to create the above metrics(revenue,margin, profit, expense...)? Please share sample data of your original table and post expected result based on the sample data.

Regards,
Lydia

[altesv · Accepted solution]

Help no longer needed. I've learned that I can create calculated accounts from my data source, so I don't need to do any further manipulation within Power BI.