Sum specific columns based on column names

[Anonymous]

Hi there,

I have a data table with this layout and I want to have a formula in column Total Value (highlighted) that automatically sum by rows only the columns that contain "Sales value", in this case: Product 1 - Sales value, Product 2 - Sales value, Product 3 - Sales value.

The idea is that if I have more products and columns, I don't have to manually add each column to the Sum formula.

Any suggestion on how I go about doing that?

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i create a table to test:

Then, unpivot it in Query Editor, after Apply&Close, it shows:

Then, choose a matrix visual and apply a visual filter to filter other columns which do not contain 'Sales value', and it shows:

Here is my test pbix.

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[Ashish_Mathur]

Hi,

The layout of your Table is not good.  In the Query Editor, right click on the first column's heading and select "Unpivot other columns".  Right click on the Attribute columns and split the column by -.  Click on any cell in the third column and go to Transformations > Pivot column.  Select Value and "Dont Aggregate".  Now drag the first column to the visual and write this measure

Revenue = SUM(Data[Sales value])

Hope this helps.

[Anonymous]

Thanks.. what if it's a calculated table and it's not possible to unpivot?

[Ashish_Mathur]

Hi,

I will not be able to help with this.

[amitchandak]

If you create a matrix(with product on columns ) with row total. and then filter product then it should work in the same manner?