cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sivapratap Member
Member

Add multiple columns as X axis in stack column chart

Hi All.

 

I have a requirement something similar to below:

 

Data:

 

Emp                 Group 1            Group 2          Group 3

AGroup 1  
B Group 2 
CGroup 1  
D Group 2 
E  Group 3
FGroup 1  

 

I need to show this data in line and stacked column chart with X Axis as group 1, group 2, group 3 and Y axis as count against each group.

 

Thanks in advance

 

 

 

sivaaprataap
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Add multiple columns as X axis in stack column chart

Hi @sivapratap ,

 

Please check the following step as below.

 

1. Unpivot the table in power query. Please check the M code for your reference.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIvyi8tUDAEskAoVidayQnChkgYwUSdsap1warWFaoCJmEMFnXDNCEWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Emp = _t, #"Group 1" = _t, #"Group 2" = _t, #"Group 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp", type text}, {"Group 1", type text}, {"Group 2", type text}, {"Group 3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Attribute] <> "Emp"))
in
    #"Filtered Rows"

Capture.PNG

 

2. Then create a measure as below and add it to the line chart to get the result as we need.

 

Measure = CALCULATE(COUNTROWS('Table1 (2)'),FILTER('Table1 (2)','Table1 (2)'[Value]<>BLANK()))

2.PNG

 

Please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Add multiple columns as X axis in stack column chart

Hi @sivapratap ,

 

Please check the following step as below.

 

1. Unpivot the table in power query. Please check the M code for your reference.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIvyi8tUDAEskAoVidayQnChkgYwUSdsap1warWFaoCJmEMFnXDNCEWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Emp = _t, #"Group 1" = _t, #"Group 2" = _t, #"Group 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Emp", type text}, {"Group 1", type text}, {"Group 2", type text}, {"Group 3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Attribute] <> "Emp"))
in
    #"Filtered Rows"

Capture.PNG

 

2. Then create a measure as below and add it to the line chart to get the result as we need.

 

Measure = CALCULATE(COUNTROWS('Table1 (2)'),FILTER('Table1 (2)','Table1 (2)'[Value]<>BLANK()))

2.PNG

 

Please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sivapratap Member
Member

Re: Add multiple columns as X axis in stack column chart

Thanks a lot Frank. This really helpedSmiley Happy

sivaaprataap