Pivote with DAX

[Puja]

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.

[Anonymous]

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

[Puja]

hI @Anonymous ,

For this request , I cant use Matrix. 

Thank you for your Time.

[Anonymous · Accepted 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

[Puja]

Hi @Anonymous , I have live a model. I cant use PQ.

Thank you

[Anonymous]

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

[FreemanZ]

hi @Puja 

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

[Puja]

Hi @FreemanZ 

Where can I find this feature ?

I cant use Matrix visual 

[FreemanZ]

check like:

https://excelnotes.com/how-to-show-items-labels-in-tabular-form/#:~:text=How%20to%20Change%20Pivot%2....

[Puja]

Hi  @FreemanZ 

Looks like this feature is available in EXCEL not in Power bi