Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 .
Type | StyleNumber | Number | Code | Sales |
A | 1011621 | 1011621010 | L | 29 |
A | 1011621 | 1011621010 | M | 23 |
A | 1011621 | 1011621010 | S | 10 |
A | 1011621 | 1011621010 | XL | 23 |
A | 1011621 | 1011621010 | XS | 3 |
A | 1011621 | 1011621010 | XXL | 12 |
C | 1011621 | 1011621010 | L | 149 |
C | 1011621 | 1011621010 | M | 152 |
C | 1011621 | 1011621010 | S | 67 |
C | 1011621 | 1011621010 | XL | 112 |
C | 1011621 | 1011621010 | XS | 18 |
C | 1011621 | 1011621010 | XXL | 49 |
C | 1011621 | 1011621010 | XXS | 5 |
D | 1011621 | 1011621010 | L | 73 |
D | 1011621 | 1011621010 | M | 74 |
D | 1011621 | 1011621010 | S | 66 |
D | 1011621 | 1011621010 | XL | 67 |
D | 1011621 | 1011621010 | XS | 4 |
D | 1011621 | 1011621010 | XXL | 4 |
The output should look like this.
Thanks a lot.
Solved! Go to Solution.
Hi @Puja,
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
Hi @Puja,
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
Hi @Puja,
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
Hi @Puja,
Ok, I think you need to do these operations on your data source side. You can duplicate the table and shaping its structures to prevent the effect to the raw table relationship mappings.
Regards,
Xiaoxin Sheng
hi @Puja
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |