Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
In need help combing some data. I have been trying all morning, but I cannot figure it out. I just started with Power BI, so hopefully there is a simple solution.
I have the following table (file: Excel File😞
What I want is a graph per project with the planned hours per year (bar) and the realised hours per year (line). I want this to be cumulative (so 2013 = 2012+2013, 2014 = 2012 + 2013 + 2014 etc.). And I slicer so I can choose the project to show the graph for. Like this:
The problem I am facing is that either Project names or the years end up on the top row/headers. But I want to use both as a filter (not sure that is the right term).
Thanks!
Solved! Go to Solution.
Basically unpivot will help
I posted a similar solution here
http://community.powerbi.com/t5/Desktop/Actual-versus-projected/m-p/348465#M156474
I split the data into 2 querys on both unpivot the years.
Create a key of project and year and then merge back together
Here are the code from the Advanced Queries.
Projects
let
Source = Excel.Workbook(File.Contents("L:\Downloads\example-file.xlsx"), null, true),
Blad1_Sheet = Source{[Item="Blad1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Blad1_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", type number}, {"Column14", Int64.Type}, {"Column15", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Projectname", type text}, {"Total", Int64.Type}, {"2012", Int64.Type}, {"2013", Int64.Type}, {"2014", Int64.Type}, {"2015", Int64.Type}, {"2016", Int64.Type}, {"2017", Int64.Type}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type1",5),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Bottom Rows",{"Total"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Projectname"}, "Attribute", "Value"),
#"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Projectname", "Projectname - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Attribute", "Attribute - Copy"),
#"Merged Columns" = Table.CombineColumns(#"Duplicated Column1",{"Projectname - Copy", "Attribute - Copy"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"ProjectYearKey"),
#"Merged Queries" = Table.NestedJoin(#"Merged Columns",{"ProjectYearKey"},Realised,{"ProjectYearKey"},"Realised",JoinKind.LeftOuter),
#"Expanded Realised" = Table.ExpandTableColumn(#"Merged Queries", "Realised", {"Value"}, {"Realised.Value"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Realised",{{"Attribute", "Year"}, {"Value", "Planned"}, {"Realised.Value", "Realised"}})
in
#"Renamed Columns"
Realised
let
Source = Excel.Workbook(File.Contents("L:\Downloads\example-file.xlsx"), null, true),
Blad1_Sheet = Source{[Item="Blad1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Blad1_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", type number}, {"Column14", Int64.Type}, {"Column15", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Projectname", type text}, {"Total", type number}, {"2012", Int64.Type}, {"2013", Int64.Type}, {"2014", Int64.Type}, {"2015", type number}, {"2016", Int64.Type}, {"2017", Int64.Type}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type1",5),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Bottom Rows",{"Total"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Projectname"}, "Attribute", "Value"),
#"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Projectname", "Projectname - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Attribute", "Attribute - Copy"),
#"Merged Columns" = Table.CombineColumns(#"Duplicated Column1",{"Projectname - Copy", "Attribute - Copy"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"ProjectYearKey")
in
#"Merged Columns"
Basically unpivot will help
I posted a similar solution here
http://community.powerbi.com/t5/Desktop/Actual-versus-projected/m-p/348465#M156474
I split the data into 2 querys on both unpivot the years.
Create a key of project and year and then merge back together
Here are the code from the Advanced Queries.
Projects
let
Source = Excel.Workbook(File.Contents("L:\Downloads\example-file.xlsx"), null, true),
Blad1_Sheet = Source{[Item="Blad1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Blad1_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", type number}, {"Column14", Int64.Type}, {"Column15", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Projectname", type text}, {"Total", Int64.Type}, {"2012", Int64.Type}, {"2013", Int64.Type}, {"2014", Int64.Type}, {"2015", Int64.Type}, {"2016", Int64.Type}, {"2017", Int64.Type}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type1",5),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Bottom Rows",{"Total"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Projectname"}, "Attribute", "Value"),
#"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Projectname", "Projectname - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Attribute", "Attribute - Copy"),
#"Merged Columns" = Table.CombineColumns(#"Duplicated Column1",{"Projectname - Copy", "Attribute - Copy"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"ProjectYearKey"),
#"Merged Queries" = Table.NestedJoin(#"Merged Columns",{"ProjectYearKey"},Realised,{"ProjectYearKey"},"Realised",JoinKind.LeftOuter),
#"Expanded Realised" = Table.ExpandTableColumn(#"Merged Queries", "Realised", {"Value"}, {"Realised.Value"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Realised",{{"Attribute", "Year"}, {"Value", "Planned"}, {"Realised.Value", "Realised"}})
in
#"Renamed Columns"
Realised
let
Source = Excel.Workbook(File.Contents("L:\Downloads\example-file.xlsx"), null, true),
Blad1_Sheet = Source{[Item="Blad1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Blad1_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", type number}, {"Column14", Int64.Type}, {"Column15", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Projectname", type text}, {"Total", type number}, {"2012", Int64.Type}, {"2013", Int64.Type}, {"2014", Int64.Type}, {"2015", type number}, {"2016", Int64.Type}, {"2017", Int64.Type}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type1",5),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Bottom Rows",{"Total"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Projectname"}, "Attribute", "Value"),
#"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Projectname", "Projectname - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Attribute", "Attribute - Copy"),
#"Merged Columns" = Table.CombineColumns(#"Duplicated Column1",{"Projectname - Copy", "Attribute - Copy"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"ProjectYearKey")
in
#"Merged Columns"
Thanks! This works.
How can I show the cumulative values for each project?
I tried:
CUMULATIVE = CALCULATE ( SUM ( Hours[Realised] ); FILTER( ALL ( Hours); Hours[Year] <= MAX( Hours[Years] ) ))
But this shows the total of all projects, instead of only the selected one.
Thanks!
---
Found it: need to use ALLSELECTED instead of ALL.
cool.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |