Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Puja
Helper III
Helper III

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.

Puja_0-1678388494024.png

 

Thanks a lot.

 

1 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:

1.PNG

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

9 REPLIES 9
v-shex-msft
Community Support
Community Support

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.

1.PNG

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

hI @v-shex-msft ,

For this request , I cant use Matrix. 

Thank you for your Time.

 

 

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:

1.PNG

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft , I have live a model. I cant use PQ.

 

Thank you

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
FreemanZ
Super User
Super User

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

Hi @FreemanZ 

 

Where can I find this feature ?

Puja_0-1678392343993.png

I cant use Matrix visual 

 

Hi  @FreemanZ 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors