Summarize values from variable fields based on year... is it even possible?

[Anonymous]

Below are 2 tables for examples. Top table has example raw data and bottom table is 1 possible way to demonstrate my desired outcome.

Each Project has 5 yrs of budget for different categories... here I'm only showing 5 projects and 2 categories (software and contract Labor). In reality, there will be 100s of projects each year and about 30 different budget categories and obviously the year numbers will increase as each year passes
I would like to be able to report the sum of the budget, for each category, for any given year... See the lower table. The output could use slicers in conjuction with a matrix table in order to filter and drill down as needed to get to the summarized totals but any solution is acceptable.

The field names are set and cannot be changed in order to capture the rolling 5 years regardless of starting year.

I am a DAX baby so if the solution is DAX, please be specific in your steps and syntax.

Any help is appreciated.

Entry_ID	Proj_Num	Proj_Name	Year_1	Software_Yr_1	Software_Yr_2	Software_Yr_3	Software_Yr_4	Software_Yr_5	Contract_Labor_Yr_1	Contract_Labor_Yr_2	Contract_Labor_Yr_3	Contract_Labor_Yr_4	Contract_Labor_Yr_5
1	p0001	Proj A	2018	100	100	100	100	100	100	100	100	100	100
2	p0002	Proj B	2019	200	200	200	200	200	200	200	200	200	200
3	p0003	Proj C	2020	300	300	300	300	300	300	300	300	300	300
4	p0004	Proj D	2021	400	400	400	400	400	400	400	400	400	400
5	p0005	Proj E	2022	500	500	500	500	500	500	500	500	500	500
For 2022				Software	Contract_Labor
1	p0001	Proj A		100	100
2	p0002	Proj B		200	200
3	p0003	Proj C		300	300
4	p0004	Proj D		400	400
5	p0005	Proj E		500	500
			Total	1500	1500

[Anonymous]

I was able to open the file on my personal PC, save it and then open on my work PC.
Thank you for the solution. I think I will be able to duplicate the transformations on my real world data.

[alena2k]

I am sorry that I confused you. I was trying to offer solution without DAX. Power Query allows you to prepare data for visualizaiton and avoid complex measures.

[Anonymous]

OK, all i see is DAX code so not sure how to interpret that as being a wisiwig solution.

I've responded with a link to a small PBIX file with the data shown in my OP if you would like to put the solution there and save (I've made anyone with the link able to edit the file.

[Anonymous]

Here is a PBIX file with the data to use in your solutions.

PBIX file 

[alena2k]

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.

[Anonymous]

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.

[alena2k · Accepted solution]

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.

[Anonymous]

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?