create group from multiple column

[Anonymous]

Hi,

I have my data in this format:

Area	Year	Sales Amount	Quantity Sold	Avg Ratio	Avg Receivable	Avg Outstanding	Avg Quantity
North	2020	$3000	112	60%	$2500	$500	100
South	2021	$4000	129	40%	$3200	$800	140
North	2021	$3500	158	30%	$3000	$500	150
West	2020	$3200	132	50%	$2000	$1200	125

I have to present this data on Matrix visual by placing Area in Rows, Year in Columns and remaining columns in rows. I need to create groups on columns so Avg ones can be in one group "Average Group" and remaining column in 2nd group "Others Group". This group i need to place in Rows under Area column.

I tried by using unpivoting the columns but it didn't work as it creates data formatting and other issues.

Please suggest how to achieve this.

Note: This is just a sample data, in actual it has more than 30 columns where i have to create 6 groups.

[Anonymous]

Hi @PBICommunity , I am still looking for the solution.

[Anonymous]

Hi @PBICommunity , I am still looking for the solution.

[Jayaselvan]

Hello,

Could you please share the pbi file or atleast a sample pbi file. Or atleast Source file with simple inputs. 

Please post expected sample output image that would be much helpful.

[Anonymous]

Hi @Jayaselvan ,

the output should be like this: