Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
Anonymous
Not applicable

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

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_IDProj_NumProj_NameYear_1Software_Yr_1Software_Yr_2Software_Yr_3Software_Yr_4Software_Yr_5Contract_Labor_Yr_1Contract_Labor_Yr_2Contract_Labor_Yr_3Contract_Labor_Yr_4Contract_Labor_Yr_5
1p0001Proj A2018100100100100100100100100100100
2p0002Proj B2019200200200200200200200200200200
3p0003Proj C2020300300300300300300300300300300
4p0004Proj D2021400400400400400400400400400400
5p0005Proj E2022500500500500500500500500500500
              
              
For 2022   SoftwareContract_Labor        
1p0001Proj A 100100        
2p0002Proj B 200200        
3p0003Proj C 300300        
4p0004Proj D 400400        
5p0005Proj E 500500        
   Total15001500        
1 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.

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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
Resolver IV
Resolver IV

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
Not applicable

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
Not applicable

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

 

PBIX file 

alena2k
Resolver IV
Resolver IV

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
Not applicable

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.

Anonymous
Not applicable

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?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.