About this project

The aim of this project is to find out what is the best three-candy combination you'll give out on Halloween to guarantee that trick-or-treaters of all tastes find something they'll love, using online votes ranking 85 types of candy.

About the data

The dataset consists of an 86-row Excel file containing the following information:

• competitorname: The name of the candy
• chocolate: Does it contain chocolate?
• fruity: Is it fruit flavored?
• caramel: Is there caramel in the candy?
• peanutalmondy: Does it contain peanuts, peanut butter or almonds?
• nougat: Does it contain nougat?
• crispedricewafer: Does it contain crisped rice, wafers, or a cookie component?
• hard: Is it a hard candy?
• bar: Is it a candy bar?
• pluribus: Is it one of many candies in a bag or box?
• sugarpercent: The percentile of sugar it falls under within the data set
• pricepercent: The unit price percentile compared to the rest of the set
• winpercent: The overall win percentage according to 269,000 matchups

Data cleansing and transformations

In order to find the best combination of 3 candy, I first had to iterate the list of sweets three times with each other using CrossJoin.

At first I approached the problem by building tables with DAX to find the best combination based on the popularity of the candy.

I then considered that creating an interactive dashboard would enhance the user experience by allowing end users to customize their candy pack according to their specific preferences. As the project evolved and the requirements became more complex, I opted to utilize Power Query instead of calculated tables, as it provides greater flexibility in transforming the data.

Once all the transformations were finished, I obtained a table with the following columns, which allowed me to create the dashboard:

· Combination: Combination of the three candies · Attribute: What are the total attributes of the candy pack? · Total Winpercent: Winpercent average of the three candies · Total SugarPercent: Sugarpercent average of the three candies · Total PricePercent: Pricepercentaverage of the three candies · SugarLevel: Sugar level from 1 to 10 according to Total SugarPercent · PriceLevel: Price level from 1 to 10 according to Total Total PricePercent

If you want to know more about the transformations made, I leave the M code at the end of the project description.

DAX Measures

I have raised this dashboard based on three key questions:

1. What characteristics do you want your pack to have?
2. How much sugar do you want your pack to have?
3. How expensive do you want your pack to be?

To answer the first question I needed to create a measure to ensure that the candy pack has at least all the characteristics selected in the segmenter.

The measure is as follows:

IsProductFiltered= 
 
VAR AtributosSeleccionados = VALUES(DimCharacteristics[Characteristics])
VAR TotalAtributosSeleccionados = COUNTROWS(AtributosSeleccionados)

RETURN
    IF(
        NOT ISFILTERED(DimCharacteristics[Characteristics]),
        0,
        VAR AtributosDelProducto = CALCULATETABLE(
            VALUES('CrossJoin'[Attribute]), 
            'CrossJoin'
        )
        VAR TotalAtributosDelProducto = COUNTROWS(
            FILTER(
                AtributosSeleccionados,
                CONTAINS(AtributosDelProducto, 'CrossJoin'[Attribute], DimCharacteristics[Characteristics])
            )
        )
        RETURN
            IF(TotalAtributosDelProducto = TotalAtributosSeleccionados, 1, 0)
    )

This is the key measure to be able to filter the combinations table in order to meet the criteria selected in the slicer. I have then sorted the combinations from most to least popular to obtain the ideal combination.

Final comments

I found this challenge particularly difficult as there was only one question to answer.

However, it forced me to put into practice DAX code transformations that I don't usually use in my analysis.

All in all, I found it a very interesting project!

I hope this Dashboard will help you find your ideal pack of candy to become the most popular house on your block!!

Annex

I leave you the M code of the transformations made in power Query in case you are interested.

Halloween Candy Transformations