Forum Discussion
Variable percentiles for different groups
I would like to have a summarized view different product groupings within my dataset. My data is in the following form:
I have n many variables, each row indicating a particular group the customer belongs to. I would like to have a percentiles view for each group type. The final dataset would take the following form for Group 1:
This view would show the front end user how the particular group behaves across the different variable types.
See below link to synthetic example dataset:
https://www.dropbox.com/sh/1ynxde6ru1ogx0v/AAChiYE1ur_CE_jRrvAInp54a?dl=0
I have done quite a bit of searching through the forums, looking for a way to create this interface - without any success. Could you please advise.
Hi Anonymous ,
To unpivot the table like this. Then you can get the excepted result by the transformed table.
M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVZbjhxHDLvLfjuB3o8T5BCGz5AgQO4fdpdqJjOucYDdRTfQXKkokqrv37/++PvPf/7ir29f+PmtC3/perLr4fdIybJinbfybkm5vv7x7R2bG+vra1ct/Mr9li1NzGa/xq5CQkzO1OutqJK4Lc7QC8bPsq4d4my5sKHFlOxHbNlg+f6Y1YvZq1f/ZSbtkmeovjElwUquq4k2PJt5H7H5wK6vtZTEiFZdNJscn+o+savn9sR8gmod18CTlp2xUXPU1XIWeKWk1QRTlQjJebixhTFQK4IaeFUVU07uWzwH6IPgVaeEJanXYaktm6zO87n5+89s80Ki9JrtRZOR0llSN/Ypqaqmzq5hrTDmlDjPx+O1LIOXEr8FiGlBE835gagb+yxrzESqvEyAQYUK9Qeov0oKXrOr0HiPU6NuSxygW8jzMSyj0eOfFEsPyg88begoF37PtB77NGPMTa/jkQ2VF/tA80qxabICybpE/TPUHmGhe5aSajE8uV4qvpVzwOqUnFTyDIsa56F9Mo1zw/qIt1WHFHpyG6i3CZtU/Q92TuskFOFzWsFsL1F9xj471qSChqxHi6mKqMgzdGuRNqvuLrLdk4GE4zNPuqU4nhWEim6ekG2JV/rQ8J7sSBFKRM2S9QaCYYJkPWN3IPtonuyK1dXx5SIutXNZ2Z4dVYRmqLcsMWKdIGSpz6eVN0VFw7Q0CdUJjVDoWYyyM3Udr1SwtWJyESAPqzwrSvZoRhVk2JHkk+vYB+DKzjzxw+5LjFiOCaayx3mYj7q/RoW+Y3mYgW2hC51/FOwspJ+hz6hoYYJ5ZvfAwXABFHmEvl0O2JDerSPjIOEijV8ieYtYrpCZmNOrJqsdkfmKxLHgutGw8hWP2udu88VzBM0VNy27CtTfsvR8oGhqjoBhTSRg+MO7ou9uHeRudC9JvX0+K4cVgWjtJ+BOtFFRBC4eQTqaYkQUtx6RrzeYy17w2nI+BmTwAGefgDscxi4CHWTk2pqIclwqsLqO03xsx4lulIHPMufQVbiS+LHZjRz9YK926gDpvmjyqvjjXw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Groupings = _t, Var_1 = _t, Var_2 = _t, Var_3 = _t, Var_4 = _t, Var_5 = _t, Var_6 = _t, Var_7 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Groupings", type text}, {"Var_1", type text}, {"Var_2", Int64.Type}, {"Var_3", Int64.Type}, {"Var_4", Int64.Type}, {"Var_5", type text}, {"Var_6", type text}, {"Var_7", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Groupings"}, "Attribute", "Value") in #"Unpivoted Other Columns"
2 Replies
Hi Anonymous
I think a good start would be Unpivoting your Var Columns in Query Editor,
Then you can create Measures to calculate your Avg and Procentails ( PERCENTILE.EXC or PERCENTILE.INC )
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
Hi Anonymous ,
To unpivot the table like this. Then you can get the excepted result by the transformed table.
M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVZbjhxHDLvLfjuB3o8T5BCGz5AgQO4fdpdqJjOucYDdRTfQXKkokqrv37/++PvPf/7ir29f+PmtC3/perLr4fdIybJinbfybkm5vv7x7R2bG+vra1ct/Mr9li1NzGa/xq5CQkzO1OutqJK4Lc7QC8bPsq4d4my5sKHFlOxHbNlg+f6Y1YvZq1f/ZSbtkmeovjElwUquq4k2PJt5H7H5wK6vtZTEiFZdNJscn+o+savn9sR8gmod18CTlp2xUXPU1XIWeKWk1QRTlQjJebixhTFQK4IaeFUVU07uWzwH6IPgVaeEJanXYaktm6zO87n5+89s80Ki9JrtRZOR0llSN/Ypqaqmzq5hrTDmlDjPx+O1LIOXEr8FiGlBE835gagb+yxrzESqvEyAQYUK9Qeov0oKXrOr0HiPU6NuSxygW8jzMSyj0eOfFEsPyg88begoF37PtB77NGPMTa/jkQ2VF/tA80qxabICybpE/TPUHmGhe5aSajE8uV4qvpVzwOqUnFTyDIsa56F9Mo1zw/qIt1WHFHpyG6i3CZtU/Q92TuskFOFzWsFsL1F9xj471qSChqxHi6mKqMgzdGuRNqvuLrLdk4GE4zNPuqU4nhWEim6ekG2JV/rQ8J7sSBFKRM2S9QaCYYJkPWN3IPtonuyK1dXx5SIutXNZ2Z4dVYRmqLcsMWKdIGSpz6eVN0VFw7Q0CdUJjVDoWYyyM3Udr1SwtWJyESAPqzwrSvZoRhVk2JHkk+vYB+DKzjzxw+5LjFiOCaayx3mYj7q/RoW+Y3mYgW2hC51/FOwspJ+hz6hoYYJ5ZvfAwXABFHmEvl0O2JDerSPjIOEijV8ieYtYrpCZmNOrJqsdkfmKxLHgutGw8hWP2udu88VzBM0VNy27CtTfsvR8oGhqjoBhTSRg+MO7ou9uHeRudC9JvX0+K4cVgWjtJ+BOtFFRBC4eQTqaYkQUtx6RrzeYy17w2nI+BmTwAGefgDscxi4CHWTk2pqIclwqsLqO03xsx4lulIHPMufQVbiS+LHZjRz9YK926gDpvmjyqvjjXw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Groupings = _t, Var_1 = _t, Var_2 = _t, Var_3 = _t, Var_4 = _t, Var_5 = _t, Var_6 = _t, Var_7 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Groupings", type text}, {"Var_1", type text}, {"Var_2", Int64.Type}, {"Var_3", Int64.Type}, {"Var_4", Int64.Type}, {"Var_5", type text}, {"Var_6", type text}, {"Var_7", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Groupings"}, "Attribute", "Value") in #"Unpivoted Other Columns"
