topic Re: table to bar chart in DAX Commands and Tips

table to bar chart

zachtatum — Thu, 01 Jun 2023 20:04:47 GMT

I'm confused about how to reorganize this data.

I would like to show a bar chart. Each bar is a year, and shows the total GRAC contribution from the projects. I suspect that I need to create a virtual table...

x-axis = year

y-axis = sum of GRAC for that year

Here's what my data looks like.

ProjectName	GRaC 2023	GRaC 2024	GRaC 2025	GRaC 2026	GRaC 2027	GRaC 2028
project apple	2.15	2.49	3.03	1.22
project orange	0	3.45	6.97	7.53
project bananna		0.75	3.2	3.82	3.82
project watermelon		7.81	9.63	10.29	0.29
project tangerine		0.64	6.58	6.62	4.9

Re: table to bar chart

lbendlin — Sat, 03 Jun 2023 21:48:49 GMT

First step is to unpivot the data to bring it into a usable form

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7LCgIxDEV/Zeh6CH0/vmWYRZQiytiWUvD3bUNRB1wkZ5Gc5G4bKzU/4rUtWMoR2cokCEPQoUMBVx0CpOxYqPb1a+WK6TY0Tst6qBaC63Bg1F/ngglTwjnj4Ay5krr/wVl7YYv1GY+cpunAi44AliJykIHuEc5uGynrPcXPU6spqfEEO95pmOL+Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectName = _t, #"GRaC 2023" = _t, #"GRaC 2024" = _t, #"GRaC 2025" = _t, #"GRaC 2026" = _t, #"GRaC 2027" = _t, #"GRaC 2028" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ProjectName"}, "Attribute", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Currency.Type}}) in #"Changed Type"

Then the visual writes itself.

Re: table to bar chart

zachtatum — Tue, 06 Jun 2023 14:14:59 GMT

Thank you.

It worked.

Is there a way that I can unpivot to a new table like you propose, but also still keep the new unpivoted table synced to the source table?

Re: table to bar chart

lbendlin — Tue, 06 Jun 2023 14:20:12 GMT

Yes, you do the unpivoting as part of the ETL in Power Query.

Re: table to bar chart

zachtatum — Tue, 06 Jun 2023 14:39:20 GMT

Got it. Thank you very much for you time helping me.