Halloween Challenge

In this challenge I’m trying to find the answer to this question:

Can we pick the 3 most popular treats to give out on Halloween to become the most popular house and guarantee that trick-or-treaters of all tastes find something they'll love?

The tool of choice is Power BI

Thought process

We need to make sure all ingredients and candy types are being offered, to make sure everyone stopping at our house will find something they’ll love.

Second objective is to become the most popular house, so we’ll optimize on the winpercent value.

Power Query

we need a couple of tables.

one of the tables will be the original CSV without any transformations, called 'candy-attributes'

Create a table with all possible permutations

I’ve done this by creating a table with the candy name and a second column with value ‘candy’ and selfjoining twice on this column to get this result:

To remove the duplicates where the same candy appears in multiple columns, I've taken these steps:

Step 1

Add a new column that concatenates the values of Candy 1, Candy 2, and Candy 3 in a sorted order. This will ensure that permutations like (Twix, Snickers, Nerds) and (Snickers, Twix, Nerds) are treated as the same combination.

Text.Combine(List.Sort({[Candy 1], [Candy 2], [Candy 3]}), ", ")

Remove Duplicates on this new column

Step 2

Add a conditional column that checks if any of the candies are identical.

If [Candy 1] = [Candy 2]

or [Candy 1] = [Candy 3]

or [Candy 2] = [Candy 3],

then set the column value to Remove

else set the column value to Keep

The reason is that we want 3 different kinds of candy and not pick 100 Grand as first and second option.

We can now remove all rows with value ‘Remove'

Step 3

Remove the 2 created columns as they are no longer needed

Create tables based on the dictionary

I’ve decided to create 3 different tables from the dictionary:

Ingredients

Types

Percentages-info

DAX Columns

Now we can create the calculated columns to check if all ingredients and candy types are being selected:

The main filter is on:

all ingredients covered? = 
SWITCH(
    TRUE(),
    'Candy permutations'[fruity] = 0, "no",
    'Candy permutations'[chocolate] = 0, "no",
    'Candy permutations'[caramel] = 0, "no",
    'Candy permutations'[peanutyalmondy] = 0, "no",
    'Candy permutations'[nougat] = 0, "no",
    'Candy permutations'[crispedricewafer] = 0, "no",
    'Candy permutations'[hard] = 0, "no",
    'Candy permutations'[bar] = 0, "no",
    'Candy permutations'[pluribus] = 0, "no",
    "yes"
)

For the percent values I’m taking the average score:

winpercent = 
VAR _candy1 = LOOKUPVALUE('candy-attributes'[winpercent],'candy-attributes'[Candy name],'Candy permutations'[Candy 1])
VAR _candy2 = LOOKUPVALUE('candy-attributes'[winpercent],'candy-attributes'[Candy name],'Candy permutations'[Candy 2])
VAR _candy3 = LOOKUPVALUE('candy-attributes'[winpercent],'candy-attributes'[Candy name],'Candy permutations'[Candy 3])

RETURN
    DIVIDE( (_candy1 + _candy2 + _candy3) , 3 )

Visuals

I’ve taken an explanatory approach as there seems to be only 1 possible outcome.

So the main question is on top and the answer is below.

In the end I’ve opted to show the top 14 candy combinations. The reason for this number is that Snickers is in all of the 13 most popular combinations and I wanted to show at least 1 other combination in case one of the top choices would not be available in the store.

On the left side I’ve shown the ingredients and types to indicate what the selection criteria were.

There is a button below the top 14 table to provide an explanation of the 3 percent values.

You can click the candy bag to get the shopping recommendation for this year to become the most popular house during Halloween.