Forum Discussion
Sum specific columns based on column names
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?
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.
Hope this helps.
Best Regards,
Giotto Zhi
5 Replies
- amitchandak
Super User
If you create a matrix(with product on columns ) with row total. and then filter product then it should work in the same manner?
- 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.
- Ashish_Mathur
Hi,
I will not be able to help with this.
- v-gizhi-msft
Community Support
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.
Hope this helps.
Best Regards,
Giotto Zhi
