Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Pivote with DAX

Hi All,

Can someone help me with Pivot with DAX.

I tried few solutions in this community and did not work for me. 

Here is my test data .

 

TypeStyleNumberNumberCodeSales
A10116211011621010L29
A10116211011621010M23
A10116211011621010S10
A10116211011621010XL23
A10116211011621010XS3
A10116211011621010XXL12
C10116211011621010L149
C10116211011621010M152
C10116211011621010S67
C10116211011621010XL112
C10116211011621010XS18
C10116211011621010XXL49
C10116211011621010XXS5
D10116211011621010L73
D10116211011621010M74
D10116211011621010S66
D10116211011621010XL67
D10116211011621010XS4
D10116211011621010XXL4

 

The output should look like this.

 

Thanks a lot.

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous,

    You can enter to query edit and choose code and sale fields to use pivot column feature on it, then you table will convert to the format that you wanted. You can use all these fields in table visual to reproduce the effects:

    Full query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZAxDoQgFETv8msLBhXWcqMlVjYkhvtfY/2zJnaMhQmSl8fMnKd9bTAEIEU8p4Bw/ZTri4u1oUvtTo2KOnihqFpeyarbNEUbIrm1XxPTojDviVnaPFrKivpH09nYFB+J0ac7VPpmYlt/kTwqygfJk6K4R1IU89+z9TC3ySfvNay1Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, StyleNumber = _t, Number = _t, Code = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"StyleNumber", Int64.Type}, {"Number", Int64.Type}, {"Code", type text}, {"Sales", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Code]), "Code", "Sales", List.Sum)
in
    #"Pivoted Column"

    Regards,

    Xiaoxin Sheng

9 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Anonymous 

    you dont need any code,
    pull Type, StyleNumber, Number to rows, Code to columns, Sales to values.
     
    the tricky point might be setting the pivot table layout.
    Step 1. Click on design -> report layout -> Show in Tabular Form.
    Step 2. Click on design -> report layout -> Repeat All Item Labels
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous,

    You can refer to the following step to achieve the output as your snapshot.

    1. Create matrix with type, style Number, number as row, code as column, sale as value.

    2. Format panel -> visual tab-> row header ->options, turn off the stepped layout.

    3. Turn of the row subtotal.

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      hI Anonymous ,

      For this request , I cant use Matrix. 

      Thank you for your Time.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous,

        You can enter to query edit and choose code and sale fields to use pivot column feature on it, then you table will convert to the format that you wanted. You can use all these fields in table visual to reproduce the effects:

        Full query:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZAxDoQgFETv8msLBhXWcqMlVjYkhvtfY/2zJnaMhQmSl8fMnKd9bTAEIEU8p4Bw/ZTri4u1oUvtTo2KOnihqFpeyarbNEUbIrm1XxPTojDviVnaPFrKivpH09nYFB+J0ac7VPpmYlt/kTwqygfJk6K4R1IU89+z9TC3ySfvNay1Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, StyleNumber = _t, Number = _t, Code = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"StyleNumber", Int64.Type}, {"Number", Int64.Type}, {"Code", type text}, {"Sales", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Code]), "Code", "Sales", List.Sum)
in
    #"Pivoted Column"

        Regards,

        Xiaoxin Sheng