Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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 |
Solved! Go to 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank 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?
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you very much!
User | Count |
---|---|
84 | |
77 | |
76 | |
43 | |
36 |
User | Count |
---|---|
109 | |
56 | |
52 | |
48 | |
43 |