how to combine multiple column in to one column

[SAPpowerbi]

Hello ,

I have multiple Category year wise column , i want combine all and used as filter .

Source data-

Cat -2016	Cat -2017	Cat-2018	Cat-2019	Value
In	Blank	Blank	In	10
Blank	In	In	In	20
Blank	blank	Blank	In	20
In	In	In	Blank	10

On above dummy table i have category wise , cat-2016 , cat-2017 ,cat-2018 ,cat-2019 year data with value .

Output – I need all below category  in one filter (Slicer) for selection .

Slicer –

ALL

cat 2016

cat 2017 

cat 2018

cat 2019 

whenever I am select cat 2016 on filter , I want only blank category values - 40 (like 20+20)

whenever I am select cat 2017 on filter I want only blank values like - 20   (Like 10+20 = 30) ,same for all...(Cat-2018,cat-2019)

and last, I want all in filter as well , whenever I select all I want all – 60  (like sum of all the values – (10+20+20+10)

Kindly help me to solve above post .

Thank you .

[PaulDBrown]

You need to unpivot the category columns in Power Quey

[SAPpowerbi]

Hello , Thanks for reply 

I already try this option to unpivot for category column but when we select "All"  from Slicer it will give incorrect all values , because Category multiply the rows in table .for Example -

Like currently All values showing - 60   but when we select all from Slicer it will multiply 4 times as category value -300 this wrong for me .
Please help me to solve this .

[v-easonf-msft · Accepted solution]

Hi, @SAPpowerbi 

You may need to add a new measure to replace the value.

M_value = 
VAR _tab =
    SUMMARIZE ( filter(Table1,Table1[Column2]="Blank"), Table1[Index], "v1", MAX(Table1[Value]))
RETURN
    SUMX( _tab, [v1] )

Please check my attached file for more details.

Best Regards,
Community Support Team _ Eason