SWITCH ROW TO COLUMN IN POWER BI VISULATION

[Saxon10]

Hi,

I changed the data from row to column in Power BI visualization but the country still showing on the Legend. I would like to show the report in Power BI visualization by country with status not status by county.

Actual Data:

COUNTRY	UK	US	INDIA	SA	SRL	PAK	BAN	AFG	ENG
MATCHED	14	228581	219077	282124	284406	216292	179342	193704	216
NOT MATCHED		56133	65637	2590	308	68422	105372	91010	98
NOT REQUIRED	284698								284398
NOT MATCHED		56133	65637	2590	308	68422	105372	91010	98

Actual Power BI data report

Desired Result

[Anonymous · Accepted solution]

Hello @Saxon10 ,

Depending on your description, you can create a calculated table as follows.

Table_2 - UNION(

    SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","UK","Value",'Table_1'[UK]),

    SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","US","Value",'Table_1'[US]),

    SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","INDIA","Value",'Table_1'[INDIA]),

    SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","SA","Value",'Table_1'[SA]),

    SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","SRL","Value",'Table_1'[SRL]),

    SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","PAK","Value",'Table_1'[PAK]),

    SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","BAN","Value",'Table_1'[BAN]),

    SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","AFG","Value",'Table_1'[AFG]),

    SELECTCOLUMNS('Table_1',"Status",'Table_1'[COUNTRY],"Country","ENG","Value",'Table_1'[ENG])

)

Result:

I hope that's what you were looking for.

Best regards

Yuna

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Saxon10]

Thanks for your time. This is exactly I am looking for to achieve my desired result.  

[amitchandak]

@Saxon10 , are UK, US are measures then how orginal data looks like ?

[Saxon10]

The 9 column are came from multiple dataset some of the theme numbers columns and some of the column are text and numbers. it's very large database. 

[amitchandak]

@Saxon10 , Unpivot this data, You will get country and status as columns. ANd value as one more column (numeric

In matrix you can status as row, Country as column and Value in Value .

https://radacad.com/pivot-and-unpivot-with-power-bi

[Saxon10]

thanks for quick reply. I can't use unpivot option because the column came from DAX measure not part of the original data. Could you please advise is there any alternative way?