Forum Discussion

po's avatar
po
Post Prodigy
7 years ago
Solved

Transpose/unpivot certain columns - displaying as chart

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

 

 

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

5 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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

    • po's avatar
      po
      Post Prodigy
      7 years ago

      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

      • po's avatar
        po
        Post Prodigy
        7 years ago

        Hi,

         Fixed the x-axis to display units none.

         

        Just the sort to fix.  Any thoughts?

         

        Thanks