Forum Discussion
Bar chart by year
Hello Community,
I need to make a bar chart so that the consolidated investment by year is shown as follows:
The information is organized in a table like this:
| Investment | Manufactures | 2024 | 2025 | 2026 | 2027 |
| Project 1 | Factory 1 | 2.525.952.000 | |||
| Project 2 | Factory 1 | 1.942.304.000 | |||
| Project 3 | Factory 1 | 1.186.432.000 | |||
| Project 4 | Factory 3 | 220.064.000 | |||
| Project 5 | Factory 2 | 540.592.000 | |||
| Project 6 | Factory 2 | 416.208.000 | |||
| Project 7 | Factory 3 | 1.999.712.000 | 1.000.000.000 | 1.000.000.000 | 1.000.000.000 |
| Project 8 | Factory 1 | 1.301.248.000 | |||
| Project 9 | Factory 3 | ||||
| Project 10 | Factory 1 | 478.400.000 | |||
| Project 11 | Factory 1 | 287.040.000 | |||
| Project 12 | Factory 1 | 478.400.000 | 478.400.000 | 478.400.000 | |
| Project 13 | Factory 3 | 583.648.000 | 478.400.000 | 478.400.000 | 478.400.000 |
| Project 14 | Factory 2 | 717.600.000 | 717.600.000 | 717.600.000 | 717.600.000 |
| Project 15 | Factory 2 | ||||
| Project 16 | Factory 3 | 6.000.000.000 | |||
| Project 17 | Factory 1 | 1.500.000.000 | 1.500.000.000 | 1.500.000.000 | |
| Project 18 | Factory 3 | 300.000.000 | |||
| Project 19 | Factory 1 | 478.400.000 | 478.400.000 | 478.400.000 | |
| Project 20 | Factory 1 | 1.000.000.000 | 1.000.000.000 | ||
| Project 21 | Factory 1 | ||||
| Project 22 | Factory 3 | 138.736.000 | |||
| Project 23 | Factory 2 | 287.040.000 | |||
| Project 24 | Factory 2 | 133.952.000 | |||
| Project 25 | Factory 3 | 956.800.000 | 717.600.000 | 717.600.000 | 717.600.000 |
| Project 26 | Factory 1 | 798.928.000 | |||
| Project 27 | Factory 3 | 669.760.000 | |||
| Project 28 | Factory 1 | 1.530.880.000 | |||
| Project 29 | Factory 1 | ||||
| Project 30 | Factory 1 | 19.136.000.000 |
But when I do it in the PowerBI I get the graph like this:
The idea is to be able to see the total of each investment per year and to be able to filter it by factory.
Hi Syndicate_Admin ,
Please refer below screenshot:
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Nice day
I would recommend using a pivot to have all the years as rows.
To do this you need to go to power query, select invert and factory columns, go to transform, unpivot, unpivot other columns. This way you can use the column where the year is to segregate the bars.
Thanks for the help, just to complement how the powerbi is in Spanish the option you indicated was:
Hi Syndicate_Admin ,
Please refer below screenshot:
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Syndicate_Admin ,
Please try below steps:
1. transform data
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZTPasMwDMZfZeRchP5YsvwCO+9eehhll10KpZe9/ZJ1zRLZcUsHDonAP+mz8ln7/fB2Pn1+HC8vNOyG1/fj5XT++vlmUFYoyoCIY/y7Drs/hANCUBKDYNpGpELIDZJ0qqQFMuHMCGidGroAJomaELR0KlgAEhkw+jaQg6Tx4KVAplsNmt635268zOxVfwQJOHXElCCmvYswZE7ZIc2KWkR0xHWxZ8A0cysiGqIqsx2t8kg4krqAzT14KuXSRZPMTBlsxh6NVimjzzbaaEP1fyz4oWJy3UgCDR7qxatsXiu4LtkiSl3/ma5zNN0Dd2OdoO3BahuHE5I4ZLHO6FpajGtbN4joIBK5Mx41qCpq4P/yHFvoRy4OhTvTgeOsMhsnlfUOWs8gFQT3HtMwTGObtPxQgMTiZTh8Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [nvestment = _t, Manufactures = _t, #"2024" = _t, #"2025" = _t, #"2026" = _t, #"2027" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"nvestment", type text}, {"Manufactures", type text}, {"2024", type text}, {"2025", type text}, {"2026", type text}, {"2027", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"nvestment", "Investment"}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Investment", "Manufactures"}, "Attribute", "Value"), #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Year"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1","","0",Replacer.ReplaceValue,{"Value"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".",",",Replacer.ReplaceText,{"Value"}) in #"Replaced Value1"2. add a clustered column chart with fields
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks for the solution, the example helped me a lot.
