Sum Columns based on Filter

[jeggen]

I am using a visualization (sanka chart) that only allows one value but the chart compares time periods. I would like the user to be able to select which values are being used on the visualization by using a slicer.

Sample data would be:

Customer	Year 1	Year 2	Year 3	Year 4	Sum*
1	10	10	20	30	70
2	100	10	20	50	180

I envisoned adding the column "Sum" above and using that for the value on my visualization. Then ideally having a slicer where the user can select which of the other columns are included in this, e.g., if they select just year 1 the visual shows values based on year 1 data, if they select year 2, just year 2 data etc. Is it possible to do something like this with a column who's data is derived from a variable combination of other columns based on a filter?

[jeggen]

Thanks! Unpivoting was the route to go.

[Anonymous · Accepted solution]

Hi @jeggen ,

You said that your chart only allows one value and plan to create a column 'Sum' to display the data.I think I could use unpivot.

The Table data is shown below:

Please follow these steps:

1.Change table structure.

2.Use the following DAX expression to create a measure

Sum = SUM('Table'[Value])

3.Final output

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