Count across multiple columns

[tockert]

I have a table with multiple team members shirt style (Men or Women) and shirt size.  

5 different rows for each one of the above, and they are named like this.

TM#1_Shirt_Style, TM#1_Shirt Size... TM#5_Shirt_Style, TM#5_Shirt_Size

      Men                            XL                   Women                   M

I need to count all of this data to summerize it down to a table with how many mens different sized shirts and the same for womens shirts.

So I end up with something like this

Style     S   M  L  XL  XXL   XXXL

Men      0    2  4   4     5       2

Women  4   3   1   1     0       0

I don't care if it totals all of that data or not.

The table is called events.

[tockert]

I am currently doing it by each team like this.

This still requires some manual counting to know all the different sizes by gender.

Thanks

[v-rzhou-msft · Accepted solution]

Hi @tockert ,

According to your screenshot, I create a sample to have a test. Here I suggest you to transform your table in Power Query Editor, then you can get result by creating a matrix visual.

M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZBLDoAgDETv0jWXEBHlt9aEsHSp3n8nHyMtXRAyvOl0QowwgYD9uc473z6fokNVh4ckIsz4QYDs8jOoIaEPhMrlwBVL2P6XwhfGNa2gUWBbsZIRgXTjOLIa8MZiMKSBgeEPLMGWFXS0oBvnPeWeBQRqIDKlFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Team Member #2 Name" = _t, #"Team Member #2 Shirt Style" = _t, #"Team Member #2 Size" = _t, #"Team Member #3 Name" = _t, #"Team Member #3 Shirt Style" = _t, #"Team Member #3 Size" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team Member #2 Name", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Other Columns", "Attribute", "Attribute - Copy"),
    #"Extracted First Characters" = Table.TransformColumns(#"Duplicated Column", {{"Attribute", each Text.Start(_, 14), type text}}),
    #"Extracted Text Range" = Table.TransformColumns(#"Extracted First Characters", {{"Attribute - Copy", each Text.Middle(_, 15, 100), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Extracted Text Range", List.Distinct(#"Extracted Text Range"[#"Attribute - Copy"]), "Attribute - Copy", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Attribute", Order.Ascending}, {"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

New Table:

Then create a matrix and get result directly.

Best Regards,
Rico Zhou

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

[goofydoggy]

Hi RicoZhou,

Do you mind advising to my query, please?

https://community.powerbi.com/t5/Desktop/How-to-create-Clustered-Column-Chart-without-counting-Blank...

Thank you!

[tockert]

Thanks for the help.  

This worked.  I was able to use this as a template to get it to work for all the team columns, as I have a total of 5 people per team.  

I now have one matrix with all the shirt style and size counts.  Saves us from having to manually count this up.

[tockert]

Here is a snip of the table from Excel that I import into Power BI.

Trying to count all of the different sized shirts by men or women.

Thanks

[amitchandak]

@tockert , It requires some pivot and unpivot to bring in shape, Can you share a better sample in table format

https://radacad.com/pivot-and-unpivot-with-power-bi