Forum Discussion

Teena_Pa4's avatar
Teena_Pa4
Frequent Visitor
7 years ago

Select columns using column name

Hi,   New to Power BI here... I have a table with a column that I need to pivot. After pivoting, I need to sum up several pivoted columns containing a specific keyword in their column names. Could ...
Teena_Pa4's avatar
Teena_Pa4
Frequent Visitor
7 years ago

Hi,

 

I have columns like Sales_A, Sales_B, Incentives, Bonus, Sales_C. How can I create a new column to sum up columns where column name contains "Sales"?

 

Thanks.

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi Teena_Pa4,

 

Can you sum them up like below?

 

Measure =
SUM ( 'table'[Sales_A] ) + SUM ( 'table'[Sales_B] ) + SUM ( 'table'[Sales_C] )

 

Best Regards,
Dale

  • Teena_Pa4's avatar
    Teena_Pa4
    Frequent Visitor
    7 years ago

    Hi,

     

    Thank you for your response.

     

    My problem is, there can be additional columns having the same prefix "Sales_". How can I dynamically add all columns that starts with "Sales_"?

     

    Thanks.

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      Hi Teena_Pa4,

       

      I'm afraid we can't add columns dynamically. Only the new rows will be included in the calculation automatically. Maybe you can unpivot the data in the Query Editor. Please refer to the snapshot below.

      Select-columns-using-column-name

       

      Best Regards,
      Dale