group column and visualization

[Anonymous]

Hello community,

I am quite new to PowerBi and have faced the following issue:
there are 4 columns with data entries which may overlap (as shown below).

 

I need to create a chart which presents as a graph the sumarized information (for example how many people has choosen lidl as first option/second etc, how many have choosen Billa as first/second etc):

How can I do that, as when I do the above chart it is taking the data only from the first column, ignoring the rest.

Thanks in advance

Martin

[Fowmy · Accepted solution]

@Anonymous 

For this you need to transform your data into two columns: Paste below M Code into blank query > Advanced editor in Power Query and check the steps. Make sure to change the file location

let
    Source = Excel.Workbook(File.Contents("C:\File.xlsx"), null, true),
    #"page-1_table-1_Sheet" = Source{[Item="page-1_table-1",Kind="Sheet"]}[Data],
    #"Transposed Table" = Table.Transpose(#"page-1_table-1_Sheet"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Options"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Value] <> ""))
in
    #"Filtered Rows"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Create a separate table by entering data.

Then create these measures.

Option1 Count = 
SWITCH (
    MAX ( 'Table (2)'[Name] ),
    "Lidl", CALCULATE ( COUNT ( 'Table'[Option1] ), 'Table'[Option1] = "Lidl" ),
    "AHTACTNKO", CALCULATE ( COUNT ( 'Table'[Option1] ), 'Table'[Option1] = "AHTACTNKO" ),
    "Kaufland", CALCULATE ( COUNT ( 'Table'[Option1] ), 'Table'[Option1] = "Kaufland" ),
    "Billa", CALCULATE ( COUNT ( 'Table'[Option1] ), 'Table'[Option1] = "Billa" )
)

Option2 Count = 
SWITCH (
    MAX ( 'Table (2)'[Name] ),
    "Lidl", CALCULATE ( COUNT ( 'Table'[Option2] ), 'Table'[Option2] = "Lidl" ),
    "AHTACTNKO", CALCULATE ( COUNT ( 'Table'[Option2] ), 'Table'[Option2] = "AHTACTNKO" ),
    "Kaufland", CALCULATE ( COUNT ( 'Table'[Option2] ), 'Table'[Option2] = "Kaufland" ),
    "Billa", CALCULATE ( COUNT ( 'Table'[Option2] ), 'Table'[Option2] = "Billa" )
)

Option3 Count = 
SWITCH (
    MAX ( 'Table (2)'[Name] ),
    "Lidl", CALCULATE ( COUNT ( 'Table'[Option3] ), 'Table'[Option3] = "Lidl" ),
    "AHTACTNKO", CALCULATE ( COUNT ( 'Table'[Option3] ), 'Table'[Option3] = "AHTACTNKO" ),
    "Kaufland", CALCULATE ( COUNT ( 'Table'[Option3] ), 'Table'[Option3] = "Kaufland" ),
    "Billa", CALCULATE ( COUNT ( 'Table'[Option3] ), 'Table'[Option3] = "Billa" )
)

Option4 Count = 
SWITCH (
    MAX ( 'Table (2)'[Name] ),
    "Lidl", CALCULATE ( COUNT ( 'Table'[Option4] ), 'Table'[Option4] = "Lidl" ),
    "AHTACTNKO", CALCULATE ( COUNT ( 'Table'[Option4] ), 'Table'[Option4] = "AHTACTNKO" ),
    "Kaufland", CALCULATE ( COUNT ( 'Table'[Option4] ), 'Table'[Option4] = "Kaufland" ),
    "Billa", CALCULATE ( COUNT ( 'Table'[Option4] ), 'Table'[Option4] = "Billa" )
)

The result is this.

You can check more details from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy · Accepted solution]

@Anonymous 

For this you need to transform your data into two columns: Paste below M Code into blank query > Advanced editor in Power Query and check the steps. Make sure to change the file location

let
    Source = Excel.Workbook(File.Contents("C:\File.xlsx"), null, true),
    #"page-1_table-1_Sheet" = Source{[Item="page-1_table-1",Kind="Sheet"]}[Data],
    #"Transposed Table" = Table.Transpose(#"page-1_table-1_Sheet"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Options"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Value] <> ""))
in
    #"Filtered Rows"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[ORDR]

You can indeed unpivot the data; but in real context you need to take in account the filters upon the main data table. How can you do that in the unpivoted table ?