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,
Have data of format below
Customer,YrWk,Mon_Delivery_BreadFlag, Tuesday Delivery_Bread_Flag.... Sunday Delivery BreadFlag,Nonday_Delivery_Milk_Flag, Tuesday_Delivery_Milk_Flag.., Sunday Delivery Milk Flag i..e all on one row for each customer/week.
i.e delivery flag for each day of week for delivery of 2 products bread and milk
example data
10,201901,Y,N,Y,N,Y......
20,201901,Y,N,N,N...
Wish to display this data in a bar chart to show the number of Customers who got bread
One chart focus on bread with day along x-axis and count of number of customers delivered on y-axis.
Similar separate chart for milk.
Can create for each day of week e.g number of customers delivered bread Monday
number of customers delivered bread Tuesday
...
number of customers delivered milk Monday
number of customers delivered milk Tuesday
with these counting the rows where set to Y in the relevant column and then creating calculated table to show worts days for deliveries i.e sorting by lowset value.
However, wondering if better approach to achieve this?
Thanks
Solved! Go to Solution.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Hi 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
Hi,
Fixed the x-axis to display units none.
Just the sort to fix. Any thoughts?
Thanks
Managed to fix by ensuirng used conditional column as per you're .pbix rather than calculated column for sort.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |