Count the number who chose each of the most popular number of options

[TM_Visual]

(data provided is an example based on the real-life structure of my data)

Each row is a food choice, associated with a person's name and their gender. There is a variable number of choices for each person.

As in real life, everyone likes Pizza.

The main graph shows the number of choice of each food type.

I would like to count the number of people who chose ALL of the most popular (x) number of options.

So in the example case:

Number of names who chose Pizza: 11

Number who chose Pizza + Cake: 7

Number who chose Pizza + Cake + Eggs: 2

Number who chose Pizza + Cake + Eggs + Pasta: 1

Number who chose Pizza + Cake + Eggs + Pasta + Fish: 0

I would like this to be re-calculated based on the most-popular results in a filtered set. For example, I would like to be able to look at new results just for Males or Females, or excluding Cake.

I can think of a way to do this in Excel by manually listing the top foods for a given combination of filters, but I would like to have something dynamic.

Is this possible?

[v-caliao-msft · Accepted solution]

@TM_Visual,

I have tested it on my local environment, the sample steps below are for you reference.

1. Pivot your table.
   
2. Add custom columns.
   = Table.AddColumn(#"Pivoted Column", "Pizza+Cake", each if [Pizza]<>0 and [Cake] <>0 then 1 else 0)
   = Table.AddColumn(#"Added Custom", "Pizza+Cake+Eggs", each if[#"Pizza+Cake"]<>0 and [Eggs]<>0 then 1 else 0)
   = Table.AddColumn(#"Added Custom1", "Pizza+Cake+Eggs+Pasta", each if [#"Pizza+Cake+Eggs"]<>0 and [Pasta]<>0 then 1 else 0)
   = Table.AddColumn(#"Added Custom2", "Pizza+Cake+Eggs+Pasta+Fish", each if [#"Pizza+Cake+Eggs+Pasta"]<>0 and [Fish]<>0 then 1 else 0)
3. Unpivot table
   

Result.

Regards,

Charlie Liao

[TM_Visual]

Thank you for your help. As far as I can tell this does the trick on a static set of data, although it can't compute new variations as simply as applying a new filter.

Despite this, it will still prove useful.

Thank you for your assistance.