Create hierarchy table based on columns from table A to create a PnL table

[Yug]

I have a table where my PnL elements are all stored as columns as in below table "Table A". And I would like to create a hierarchy table connected to this table so that I can create a voyage pnl visual. How can I create a hierarchy table that looks like the second table below, "Table B"? 

I createe a hierarchy table by entering data in new table but I cannot connect this to columns in table A to create a pnl dashboard.

Any help would be appreciated. 

Thank you

Yug 

Table A:

Voyage no	Freight	Fuel cost	Port expense	Load port exp	Discharge port expense	Misc expense	Revenue	Voyage surplus	Cargo In	Cargo Out	Cargo Result
1	500	10	100	75	64	64	4000	813	200	10	190
2	700	15	120	34	24	24	2462	917	46	40	6
3	1000	24	354	67	24	24	1155	1493	674	500	174
4	2000	100	325	28	65	65	1211	2583	576	400	176
5	800	32	350	64	14	14	2000	1274	546	490	56

Table B: 

Level 1	Level 2
Voyage surplus	Freight
Voyage surplus	Fuel cost
Voyage surplus	Port expense
Voyage surplus	Load port exp
Voyage surplus	Discharge port expense
Voyage surplus	Misc expense
Cargo result	Cargo In
Cargo result	Cargo Out

[MFelix · Accepted solution]

Hi @Yug ,

For this you need to unpivot your data and remove the Voyage and Cargo result values (since they are totals) the final resul will look like this:

Then you can do the relationship and measure to calculate correctly.

[Yug]

Thank you MFelix for your reply.

My thought was to do somewhat similar -  create another unpovited table. However, since my data table which needs unpivoting is large, (200 columns and many thousand rows and increasing), do yow know if unpivoting will have any impact the performance of the report?

[MFelix]

@Yug

The impact that yyou may have is on refresh performance but this also depends on the quantity of rows you may be having on your model. 

[Yug]

Thank you very much!