Forum Discussion

sdhn's avatar
sdhn
Responsive Resident
4 years ago
Solved

Creating new calculated columns

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 

 

 

 

 

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      sdhn
      Responsive Resident
      4 years ago

      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 

    • sdhn's avatar
      sdhn
      Responsive Resident
      4 years ago

      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)

       

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.