Forum Discussion
Post ProdigyTranspose/unpivot certain columns - displaying as chart
Hi po,
Believe that the best option is on the query editor select the columns of the weekdays flags and unpivoted them you will get a table with the followong format:
Customer YearWk Attribute Flag
10 201901 Monday_Bread Y 10 201901 Tuesday_Bread N 10 201901 Wednesday_Bread Y 10 201901 Thursday_Bread N 10 201901 Friday_Bread Y 10 201901 Saturday_Bread N 10 201901 Sunday_Bread Y 10 201901 Monday_Milk Y 10 201901 Tuesday_Milk N 10 201901 Wednesday_Milk Y 10 201901 Thursday_Milk N 10 201901 Friday_Milk Y 10 201901 Saturday_Milk N 10 201901 Sunday_Milk Y 20 201901 Monday_Bread Y 20 201901 Tuesday_Bread N 20 201901 Wednesday_Bread N 20 201901 Thursday_Bread Y 20 201901 Friday_Bread Y 20 201901 Saturday_Bread N 20 201901 Sunday_Bread N 20 201901 Monday_Milk Y 20 201901 Tuesday_Milk Y 20 201901 Wednesday_Milk Y 20 201901 Thursday_Milk N 20 201901 Friday_Milk N 20 201901 Saturday_Milk N 20 201901 Sunday_Milk Y You can then proceed to do more changes as Split row to columns to get the product and the Day of the week, add a number per week day to sort the weekday and then use it on your charts.
Check PBIX file attach with all the transformations I refer and a sample chart.
Regards,
MFelix
Hi po,
Have you tired unpivoting the MonBreadFlag, TueBreadFlag, etc ..
Then create a bar chart with filters set for all BreadFlags and another chart with filters set for MilkFlag and Values as Count of Customers.
Let me know if this works for you.
Cheers
CheenuSing
poHi All,
Thanks for replies.
This works great.
Just a couple of queries.
The x-axis has count in decimal format e.g 7.00 rather than 7 - thought we woudl be abel to change this easily on format x-axis but doesn't seem to be an option to make whole number - our store number is defined a stext as occassionalyy they put 123/124 characters within it.
Secondly if trying to sort on the sortde column in matrix without actaully including the sort column i.e. 1 to 7 doesn't look to apply sort despite fact sort the default at modeeliong level.
Any thoughts on how we can remove the decimal format on the x-axis and ensure a matrix always sorts by a speicfic column?
Thanks