Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Solved! Go to 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.
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.
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.
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.
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
71 | |
49 |
User | Count |
---|---|
141 | |
121 | |
112 | |
60 | |
58 |