Power BI: How to custom sort

1
votes

I have a Power BI custom sort question. I need to put my raw data into groups and then into sub-groups. I am struggling to do custom sort for these groups and subgroups.

Let me explain with my sample T-shirt sales data.

  1. Here's my original data with added unique key column: Gender_Size_Sleeve_OrganicOrNot
ProductID  Gender  Size   Sleeve  OrganicOrNot  UnitPrice  UnitsSold  Sales Gender_Size_Sleeve_OrganicOrNot 
    
#123456    Male    110cm  Long    Organic       $25        1          $25   Male_110cm_Long_Organic
#234567    Male    Small  Short   NonOrganic    $40        1          $40   Male_Small_Short_NonOrganic
#345678    Male    Medium Short   NonOrganic    $30        2          $60   Male_Medium_Short_NonOrganic
#456789    Female  Large  Long    NonOrganic    $55        1          $55   Female_Large_Long_NonOrganic
#567890    Female  120cm  Short   Organic       $35        1          $35   Female_120cm_Short_Organic
#678901    Female  100cm  Long    Organic       $37        1          $37   Female_100cm_Long_Organic
...
  1. My goal is to group by "Category" and subgroup by "ProductType" these data in the following manner and show the aggregated Sales:
Category  ProductType           Sales
Women     Long Sleeve           $8,250
          Organic Long Sleeve   $9,300
          Short Sleeve          $7,500
          Organic Short Sleeve  $4,200
Men       Long Sleeve           $6,000
          Organic Long Sleeve   $3,800
          Short Sleeve          $1,800
          Organic Short Sleeve  $3,250
Girls     Long Sleeve           $3,805
          Organic Long Sleeve   $6,660
          Short Sleeve          $8,805
          Organic Short Sleeve  $4,250
Boys      Long Sleeve           $3,570
          Organic Long Sleeve   $8,000
          Short Sleeve          $7,770
          Organic short Sleeve  $9,000
  1. The above "Category" and "ProductType" are defined by the following mapping table to the raw data:
Gender_Size_Sleeve_OrganicOrNot Category  ProductType           NumCategory  NumProductType
Female_Large_Long_NonOrganic    Women     Long Sleeve           1            1
Female_Midium_Long_NonOrganic   Women     Long Sleeve           1            1
Female_Small_Long_NonOrganic    Women     Long Sleeve           1            1
Female_Large_Long_Organic       Women     Organic Long Sleeve   1            2
Female_Midium_Long_Organic      Women     Organic Long Sleeve   1            2
Female_Small_Long_Organic       Women     Organic Long Sleeve   1            2
Female_Large_Short_NonOrganic   Women     Short Sleeve          1            3
Female_Midium_Short_NonOrganic  Women     Short Sleeve          1            3
Female_Small_Short_NonOrganic   Women     Short Sleeve          1            3
Female_Large_Short_Organic      Women     Organic Short Sleeve  1            4
Female_Midium_Short_Organic     Women     Organic Short Sleeve  1            4
Female_Small_Short_Organic      Women     Organic Short Sleeve  1            4
Male_Large_Long_NonOrganic      Men       Long Sleeve           2            5
Male_Midium_Long_NonOrganic     Men       Long Sleeve           2            5
Male_Small_Long_NonOrganic      Men       Long Sleeve           2            5
Male_Large_Long_Organic         Men       Organic Long Sleeve   2            6
Male_Midium_Long_Organic        Men       Organic Long Sleeve   2            6
Male_Small_Long_Organic         Men       Organic Long Sleeve   2            6
Male_Large_Short_NonOrganic     Men       Short Sleeve          2            7
Male_Midium_Short_NonOrganic    Men       Short Sleeve          2            7
Male_Small_Short_NonOrganic     Men       Short Sleeve          2            7
Male_Large_Short_Organic        Men       Organic Short Sleeve  2            8
Male_Midium_Short_Organic       Men       Organic Short Sleeve  2            8
Male_Small_Short_Organic        Men       Organic Short Sleeve  2            8
Female_100cm_Long_NonOrganic    Girls     Long Sleeve           3            9
Female_110cm_Long_NonOrganic    Girls     Long Sleeve           3            9
Female_120cm_Long_NonOrganic    Girls     Long Sleeve           3            9
Female_100cm_Long_Organic       Girls     Organic Long Sleeve   3            10
Female_110cm_Long_Organic       Girls     Organic Long Sleeve   3            10
Female_120cm_Long_Organic       Girls     Organic Long Sleeve   3            10
Female_100cm_Short_NonOrganic   Girls     Short Sleeve          3            11
Female_110cm_Short_NonOrganic   Girls     Short Sleeve          3            11
Female_120cm_Short_NonOrganic   Girls     Short Sleeve          3            11
Female_100cm_Short_Organic      Girls     Organic Short Sleeve  3            12
Female_110cm_Short_Organic      Girls     Organic Short Sleeve  3            12
Female_120cm_Short_Organic      Girls     Organic Short Sleeve  3            12
Male_100cm_Long_NonOrganic      Boys      Long Sleeve           4            13
Male_110cm_Long_NonOrganic      Boys      Long Sleeve           4            13
Male_120cm_Long_NonOrganic      Boys      Long Sleeve           4            13
Male_100cm_Long_Organic         Boys      Organic Long Sleeve   4            14
Male_110cm_Long_Organic         Boys      Organic Long Sleeve   4            14
Male_120cm_Long_Organic         Boys      Organic Long Sleeve   4            14
Male_100cm_Short_NonOrganic     Boys      Short Sleeve          4            15
Male_110cm_Short_NonOrganic     Boys      Short Sleeve          4            15
Male_120cm_Short_NonOrganic     Boys      Short Sleeve          4            15
Male_100cm_Short_Organic        Boys      Organic Short Sleeve  4            16
Male_110cm_Short_Organic        Boys      Organic Short Sleeve  4            16
Male_120cm_Short_Organic        Boys      Organic Short Sleeve  4            16
  1. I can create a matrix with my raw data table and the mapping table:

enter image description here

  1. Furthermore, I can make "Category" column Sorted by "NumCategory" column and remove "NumCategory" column from the matrix:

enter image description here

  1. I would like to remove "NumProductType" from the table but would like to keep the order of "ProductType". However, sorting "ProductType" by "NumProductType" is not allowed as there are more than one value in "NumProductType" for the same value in "ProductType":

enter image description here

Keeping this "ProductType" order is crucial to me. Would you kindly advise a walkaround?

Thanks & Regards, Kyoto

1
powerbipowerbi-desktop

1 Answers

1
votes

Ok, your main problem is that you have multiple values for the same Product Type.

This is how i would solv it.

First, duplicate the ProductType column using power query. This is super important, otherwise you will still get the same error. enter image description here

Then create a new column using the following dax formula:

Order = 
   VAR __prodType = 'sample'[ProductCopy]
   VAR __subTable = 
       FILTER( 'sample', 'sample'[ProductCopy] = __prodType )
   VAR __minValue =
       CALCULATE( MIN( 'sample'[NumProductType] ), __subTable )

Return __minValue

This dax formula create our sort column that contains an unique value for each Product Type. Note that we are using the duplicated product column instead of the original one.

Then select the original ProductType column and sort it using the Order column.

My power bi version is in spanish but you will get the idea.

enter image description here

Now create your matrix using the category and product type columns and sort it by Category

enter image description here

This is the final result:

enter image description here