topic Re: Pivote with DAX in DAX Commands and Tips

Pivote with DAX

Puja — Thu, 09 Mar 2023 19:03:22 GMT

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.

Re: Pivote with DAX

FreemanZ — Thu, 09 Mar 2023 19:49:35 GMT

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

Re: Pivote with DAX

Puja — Thu, 09 Mar 2023 20:07:30 GMT

Hi FreemanZ

Where can I find this feature ?

I cant use Matrix visual

Re: Pivote with DAX

FreemanZ — Thu, 09 Mar 2023 20:09:12 GMT

check like:

https://excelnotes.com/how-to-show-items-labels-in-tabular-form/#:~:text=How%20to%20Change%20Pivot%20Table%20in%20Tabular%20Form,Pivot%20table%20will%20change%20to%20the%20Tabular%20form.

Re: Pivote with DAX

Puja — Thu, 09 Mar 2023 20:18:59 GMT

Hi FreemanZ

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

Re: Pivote with DAX

Anonymous — Fri, 17 Mar 2023 07:47:57 GMT

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

Re: Pivote with DAX

Puja — Fri, 17 Mar 2023 12:40:39 GMT

hI Anonymous ,

For this request , I cant use Matrix.

Thank you for your Time.

Re: Pivote with DAX

Anonymous — Tue, 28 Mar 2023 09:28:39 GMT

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

Re: Pivote with DAX

Puja — Tue, 28 Mar 2023 13:24:53 GMT

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

Thank you

Re: Pivote with DAX

Anonymous — Fri, 31 Mar 2023 09:03:13 GMT

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