How to do the calculation in Dax Measure at row level?

[Anonymous]

Period

April

May

GL Code

Description

GL Type

Amount

Period

Gross Sales

100

110

a

Gross sales GL

Gross Sales

100

April

Discounts

20

30

d

Discount GL

Discount

20

April

Net Sales

80

80

calculated new field(Gross -Discount)

b

Raw Material GL

RM

50

April

RM

50

60

c

Processing Cost GL

Overhead

10

April

Overhead

10

20

a

Gross sales GL

Gross Sales

110

May

Total Expenses

60

80

calculated new field(RM + Overhead)

d

Discount GL

Discount

30

May

Net Profit

20

0

calculated new Field(Net Sales - Total Expenses)

b

Raw Material GL

RM

60

May

c

Processing Cost GL

Overhead

20

May

I want to measure Calculated New Field

[ryan_mayu]

@Anonymous 

Could you please provide the screenshot of your raw data and the expected results that you want?

[Anonymous]

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-xicai · Accepted solution]

Hi @Anonymous ,

You may enter into Query Editor, select on [GL Type], then go to Transform ->Pivot Column, don't forget to click button "Close & Apply".

Then you may create columns like DAX below, choose Matrix visual to display the series of GL Type columns or fields as you demand into Values box of matrix, put Period into Rows box, turn on the option "Show on rows" under Values option in Format pane.

Net sales = [Gross Sales]-[Discount]

Total Expenses = [RM]+[Overhead]

Net Profit = [Net sales]- [Total Expenses]

Result:

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.