Dynamic measure to apply to multiple columns

[Anonymous]

Hi all, 

I have tralled through the forum for hours now and cant quite find a solution so I am really hoping for some help with this one. 

I have a  table that when simplified looks a bit like the one below.

I need a solution as I have 164 columns with data like this (survey data) and my current solution requires a measure for each one - its not quite sustainable. 

What I would like to do is create a process where the same measure is applied depending on what column is selected in a slicer. So for example if I could sum the data depending on column selected without having to create an individual measure for each column to call. 

Category	ID	Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Column 10
ABC	1	2	2	5	4	4	4	4	4	2	3
ABC	2	2	5	5	4	4	2	4	4	2	4
ABC	3	1	5	2	5	2	2	4	4	1	5
ABC	4	5	5	2	3	2	1	3	3	5	2
ABC	5	6	6	1	3	1	5	3	3	6	6
ABC	6	6	6	5	3	5	6	5	5	6	5
DEF	7	4	6	6	2	6	6	5	5	4	5
DEF	8	4	6	6	2	6	4	3	2	6	5
DEF	9	3	3	4	1	4	4	3	2	6	5
DEF	10	6	2	4	5	4	4	3	2	6	5
DEF	11	1	2	3	6	3	5	3	2	6	5
GHI	12	4	1	6	6	6	1	5	1	6	5
GHI	13	6	5	1	4	1	4	5	5	6	4
GHI	14	4	6	4	4	4	6	2	6	4	6
GHI	15	4	6	6	3	6	4	2	6	4	5
GHI	16	5	4	4	6	4	4	1	4	5	4

Help on this would be GREATLY appreciated!

[Ashish_Mathur]

Hi,

In the Query Editor, right click on the first 2 columns and select "Unpivot other columns".  Rename the Attribute column, if you wish to.  Now drag the Attribute (or remaned) column to the slicer and build your visual.

[Anonymous]

@Ashish_Mathur 

Thanks for this, I can't unpivot this dataset unfortunately, the dataset is too big and needs to be structured this way. I am looking for a solution that doesnt require unpivoting if possible. 

Thanks so much

[Ashish_Mathur]

You are welcome.  That will not be possible.  "Data being big" cannot be a reason to not "Unpivot".

[Anonymous]

Thanks, really hoping that I wont have to unpivot. Still waiting for the query to run and thats just testing it on only a quarter of a years data; I need to merge and unpivot data for 2 years. 

Wondering if I could get help with M, wondering if something like mentioned here could be fashioned for this: https://blog.crossjoin.co.uk/2016/03/07/dynamic-column-selection-in-custom-columns-in-power-bipower-...

[ChandeepChhabra]

@Anonymous 

The Ideal way to go about this would be to unpivot the columns in Power Query. For the given table

1. Select the first 2 columns (category & id)
2. Right click and choose unpivot other columns

Then you should write a single measure which will work as expected

=SUM(Data[Col Name])

Please find the Power BI file attached here

Thanks