Forum Discussion
Summarize values from variable fields based on year... is it even possible?
Let's give it another try, still without DAX.
Please download this version of pbx file, open Power Query editor and look at data transformation for the Table(2). It uses your source data, unpivots them so Category can be unlimited and adds Year column for summarizing by year. Year is relative to the Year_1.
Table(2) is used for Matrix visual. I hope this helps.
If you could use Power Query, it take a few manipulations: unpivot and split
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndA9DoAgDAXgq5jODKVAoqN/uzvhAi4a7z9IS3uBLu+R8PINrRUiBHgRkfv6nnta+4Mwzr0iojNbqEAqk8nbkBcpdCbLSeVk8i6fxJuE3mQ5q5xNPobM18my9CTLReVi8jlkvk6RpSdb+wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Entry_ID = _t, Proj_Num = _t, Proj_Name = _t, Year_1 = _t, Software_Yr_1 = _t, Software_Yr_2 = _t, Software_Yr_3 = _t, Software_Yr_4 = _t, Software_Yr_5 = _t, Contract_Labor_Yr_1 = _t, Contract_Labor_Yr_2 = _t, Contract_Labor_Yr_3 = _t, Contract_Labor_Yr_4 = _t, Contract_Labor_Yr_5 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Entry_ID", Int64.Type}, {"Proj_Num", type text}, {"Proj_Name", type text}, {"Year_1", Int64.Type}, {"Software_Yr_1", Int64.Type}, {"Software_Yr_2", Int64.Type}, {"Software_Yr_3", Int64.Type}, {"Software_Yr_4", Int64.Type}, {"Software_Yr_5", Int64.Type}, {"Contract_Labor_Yr_1", Int64.Type}, {"Contract_Labor_Yr_2", Int64.Type}, {"Contract_Labor_Yr_3", Int64.Type}, {"Contract_Labor_Yr_4", Int64.Type}, {"Contract_Labor_Yr_5", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Entry_ID", "Proj_Num", "Proj_Name", "Year_1"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.1", "Category"}})
in
#"Renamed Columns"After such data transformation you can present it in matrix visual with Yes, Project as rows and Category as columns.
This was way over my head. As I said, I know very little about DAX.
I already have the columns/values in PowerBI. I was just presenting what the data looks like in a general table format in my original post. So I assume the upper half of the dax would not be needed.For the lower half of the solution DAX, I would need a lot more expanation as to what your intent is withe the code and what you would expect to see in the PBIX.
Let's give it another try, still without DAX.
Please download this version of pbx file, open Power Query editor and look at data transformation for the Table(2). It uses your source data, unpivots them so Category can be unlimited and adds Year column for summarizing by year. Year is relative to the Year_1.
Table(2) is used for Matrix visual. I hope this helps.I am unable to open that file as it says that the file is incompatible with my version of PowerBI.
I have version 2.96.1061.0.Are you using an older version?
Can you update your version and submit a newer version of the pbix file?