Creating new calculated columns

[sdhn]

Hi All,

I have source file as below:

I want to create thru Power Bi as:

Columns with Excel formulas:

1)

"Total 1"

=(B2+C2)

2)

"P %"

=IF(D2<=0,"N/A",(C2/D2))

 3)

"Total Amount"

=IF(D2>0,SUM(F2:G2),0)

 4)

"Average"

=IF(H2>0,(H2/D2),"N/A")

I want to use Power Bi dashboard to calculate & create above columns "Total 1" , "P %", "Total Amount", "Average" .   

Help will be apprcecaited.  Thanks 

[v-yanjiang-msft · Accepted solution]

Hi @sdhn ,

According to your description, here's my solution.

"Total 1" , "P %", "Total Amount", "Average" are all calculated columns.

Total1 = 'Table'[Col1]+'Table'[Col2]

P% =
IF (
    'Table'[Total1] <= 0,
    "N/A",
    FORMAT ( DIVIDE ( 'Table'[Col2], 'Table'[Total1] ), "0.0%" )
)

Total Amount = 'Table'[Col3]+'Table'[Col4]

Average =
IF (
    'Table'[Total Amount] > 0,
    FORMAT ( DIVIDE ( 'Table'[Total Amount], 'Table'[Total1] ), "0.000" ),
    "N/A"
)

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[amitchandak]

@sdhn , try new columns like

1)

"Total 1"

=([Col1]+[Col2])

a Measure = sum([Col1]) + Sum([Col2])

2)

"P %"

=divide([Col2], [Total])

measure =  =divide(sum([Col2]), Sum(Table[Total]) ) //if total is column

3)

"Total Amount"

= [Col3] + [Col4]

measure = sum([Col3]) + sum([Col4])

4)

"Average"

=divide([Total Amount], [Total])

[sdhn]

2)

"P %"

=divide([Col2], [Total])

measure =  =divide(sum([Col2]), Sum(Table[Total]) ) //if total is column

Total is a measure not a column

SUM(B43:C43)

[sdhn]

REF

2)

measure =  =divide(sum([Col2]), Sum(Table[Total]) ) //if total is column

Total is a measure created in 1. How measure can add above?

1) 

a Measure = sum([Col1]) + Sum([Col2])

My code status

measure =  =divide(sum([Col1]) + Sum(.........   measure is not listed