Row level calculation for making P&L account from Trial Balance

[iamsunil99]

Hi,

I am trying to build a P&L account from the monthly TrialBalance of a company and I am stuck with an issue in achieving row level calculation.

Following is my base sample data for 2 months in BI(I have separate trials for the period and I have combined them in BI )

GL Code	Description	GL Type	Amount	Period
a	Gross sales GL	Gross Sales	100	April
d	Discount GL	Discount	20	April
b	Raw Material GL	RM	50	April
c	Processing Cost GL	Overhead	10	April
a	Gross sales GL	Gross Sales	110	May
d	Discount GL	Discount	30	May
b	Raw Material GL	RM	60	May
c	Processing Cost GL	Overhead	20	May

I am trying to achieve the following output, but I am unable to do the required calculations(calculated New Fields) in row level.

	Period
	April	May
Gross Sales	100	110
Discounts	20	30
Net Sales	80	80	calculated new field(Gross -Discount)
RM	50	60
Overhead	10	20
Total Expenses	60	80	calculated new field(RM + Overhead)
Net Profit	20	0	calculated new Field(Net Sales - Total Expenses)

Kindly help me with the solution. Thanks in advance!!

[v-yiruan-msft · Accepted solution]

Hi @iamsunil99 ,

You can follow the below steps to achieve it:

1. Create a calculated table with the below formula:

Table 2 = UNION(VALUES('Table'[GL Type]),ROW("GL Type","Net Sales"),ROW("GL Type","Total Expenses"),ROW("GL Type","xNet Profit"))

2. Create a measure to get the value of Net Sales, Total Expenses and Net Profit

Measure = 
var _nets= CALCULATE(sum('Table'[Amount]),'Table'[GL Type]="Gross Sales")- CALCULATE(sum('Table'[Amount]),'Table'[GL Type]="Discount") 
var _texp=CALCULATE(sum('Table'[Amount]),'Table'[GL Type]="RM")+CALCULATE(sum('Table'[Amount]),'Table'[GL Type]="Overhead")
return switch(SELECTEDVALUE('Table 2'[GL Type]),"Net Sales",_nets,"Total Expenses",_texp,"xNet Profit",_nets-_texp,CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[GL Type]=MAX('Table 2'[GL Type]))))

3. Create a matrix visual( Rows: GL Type(from the new created table) Columns: Period  Values: Measure)

Best Regards

Rena