Forum Discussion
Display Measures in Rows and Fixed values (TY, LY, Var %) in Columns
Hi Anonymous
Based on my knowledge, it is not possible for a column or measure to contains two kinds of data types.
When you add the column or measure [Value] to the Value field of the Matrix visual, even it is listed under different columns visually, but in fact [Value] can only exsit in one column or one measure.
Here is a workaround which i list columns or measures in the Table visual, finally the Table visual is like below.
Original table
Final Table visual
Code in Advanced editor
let Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\9\9.13\9.13.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Region Name", type text}, {"TY YTD Sales", Int64.Type}, {"TY PTD Sales", Int64.Type}, {"TY WTD Sales Online", Int64.Type}, {"TY Daily Sales", Int64.Type}, {"LY YTD Sales", Int64.Type}, {"LY PTD Sales", Int64.Type}, {"LY WTD Sales Online", Int64.Type}, {"LY Daily Sales", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Region Name"}, "Attribute", "Value"), #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Attribute", "Attribute - Copy"), #"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "Attribute - Copy", Splitter.SplitTextByPositions({0, 3}, false), {"Attribute - Copy.1", "Attribute - Copy.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute - Copy.1", type text}, {"Attribute - Copy.2", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[#"Attribute - Copy.1"]), "Attribute - Copy.1", "Value"), #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Date", Order.Ascending}, {"Attribute - Copy.2", Order.Ascending}, {"Attribute", Order.Ascending}}), #"Filled Up" = Table.FillUp(#"Sorted Rows",{"TY "}), #"Filled Down" = Table.FillDown(#"Filled Up",{"LY "}), #"Removed Duplicates" = Table.Distinct(#"Filled Down", {"TY ", "Date"}) in #"Removed Duplicates"Best Regards
Maggie
Community SupportHi Anonymous
Based on my knowledge, it is not possible for a column or measure to contains two kinds of data types.
When you add the column or measure [Value] to the Value field of the Matrix visual, even it is listed under different columns visually, but in fact [Value] can only exsit in one column or one measure.
Here is a workaround which i list columns or measures in the Table visual, finally the Table visual is like below.
Original table
Final Table visual
Code in Advanced editor
let
Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\9\9.13\9.13.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Region Name", type text}, {"TY YTD Sales", Int64.Type}, {"TY PTD Sales", Int64.Type}, {"TY WTD Sales Online", Int64.Type}, {"TY Daily Sales", Int64.Type}, {"LY YTD Sales", Int64.Type}, {"LY PTD Sales", Int64.Type}, {"LY WTD Sales Online", Int64.Type}, {"LY Daily Sales", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Region Name"}, "Attribute", "Value"),
#"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Attribute", "Attribute - Copy"),
#"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "Attribute - Copy", Splitter.SplitTextByPositions({0, 3}, false), {"Attribute - Copy.1", "Attribute - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute - Copy.1", type text}, {"Attribute - Copy.2", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[#"Attribute - Copy.1"]), "Attribute - Copy.1", "Value"),
#"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Date", Order.Ascending}, {"Attribute - Copy.2", Order.Ascending}, {"Attribute", Order.Ascending}}),
#"Filled Up" = Table.FillUp(#"Sorted Rows",{"TY "}),
#"Filled Down" = Table.FillDown(#"Filled Up",{"LY "}),
#"Removed Duplicates" = Table.Distinct(#"Filled Down", {"TY ", "Date"})
in
#"Removed Duplicates"
Best Regards
Maggie
Hey v-juanli-msft,
Thank you so much!
Went through the M Code you shared and applied it to my solution and it works great!
One question i have is,in my solution i have multiple measures such as:
- Sales (Decimal Number)
- Product Margin (Decimal Number)
- Nr of Transactions (Whole Number)
- Product Margin % (Percentage)
When i use this solution for measures with Data Types of: Decimal Number or Whole Number everything works great.
However, when i try to include 'Product Margin %' this is when it gets messy.
When i add 'Product Margin %' to a visual such as a Matrix, and go to a hgiher level detail, as you can imagine the percentage value is being summed up, which displays incorrect values, however, when i drill down to the lowest level, i get the expected result.
If you have any pointers on how you would approach this, would much appreciate it.
Thanks,
Laz