Halloween is around the corner! Candy Carnage . . . Choose your treat wisely

A few days ago, I sat down for a coffee with my wonderfully sassy 80-something-year-old neighbor, Sally. Now, Sally may be a little shaky on the eyesight, but her curiosity about what I’m working on is as sharp as ever. “Hey there, handsome—what’s shaking in your world these days?” she asked. I know, I know… she probably can’t see that well, but let’s just say I wasn’t about to correct her! 😉

So, naturally, I began explaining my latest data analysis project—this year’s Maven Halloween Candy Challenge. I shared with her the top three candies based on their popularity. Well, let’s just say… Sally was not having it.

“No way am I handing out those overly sweet, overpriced little things to the kids!” she huffed. You’d think I told her the neighborhood was out of prune juice. “We need better options than that,” she declared.

Sally had a point, and in data analysis, the first rule is to know your stakeholders. So, I thought, who better than Sally to be the ultimate stakeholder for this project? After all, she’s the one doling out the candy this year.

As we sipped our coffee (well, she sipped, I nervously took notes), Sally argued with her friends (yes, some of her good friends were there too) about sugar levels and the rising cost of candy. She quickly made it clear that she didn’t care about fruity flavors or whether the candy had nuts or not. What did matter to her? Three things: sugar content, price, and popularity.

With that in mind, I decided to create a simple but hopefully effective solution:

The Plan:

1. Clean up the data: I started by introducing a column to normalize the winpercent since it was a percentage, while sugarpercent and pricepercent were already in percentiles. Consistency is key!
2. Normalize the schema: Because, you know, data cleanliness is next to godliness. But beyond the joy of playing with Power Query, creating new queries based on columns, merging queries etc. the data set was too small to really bother. So, I reversed some of what I did there.
3. Introduce 3 slicers for Sugar Level, Price Sensitivity, and Popularity Preference—making it easy for users like Sally to decide what matters most.
4. Apply a Dynamic Weighted Harmonic Mean approach to rank the candies. (Sounds fancy, but don’t worry—we’ll dive into the DAX magic soon.)
5. Introduce some measures to retrieve data about the Top 1, 2, 3 candies to be used in some custom tooltips and …
6. Design the dashboard: Make it look good, but not too flashy. After all, this is Halloween, not a Paris or candy fashion show (I may have failed there but it was fun anyway).

The Dynamic Weighted Harmonic Mean:

The approach was simple yet powerful. My intent was to give users the ability to adjust the weights for three key factors:

• Sugar Content: Whether the user wanted to prioritize low, average, or high sugar levels based on their preference.
• Price Sensitivity: Some folks don’t mind breaking the bank on premium candies, while others want to stay within budget.
• Popularity (Win Percent): You know how it goes. Some people want to be the house with the legendary candy, while others are fine with handing out whatever’s left on the grocery store shelf also known as “clearance”.

This method allowed us to customize the candy rankings to suit everyone’s preferences. Sally could focus on finding affordable, low-sugar candies, while her friend Nancy (who loves the good stuff) could crank up the popularity.

Why the Harmonic Mean?

Good question! The harmonic mean is ideal when dealing with rates and proportions—exactly what we were dealing with here (sugar, price, and popularity). It helps avoid cases where a high value in one category (like price) overshadows everything else. Instead, it focuses on balancing the smaller values—perfect for those of us (Sally) trying to keep sugar and cost down without throwing popularity out the window.

How the DAX Code Works

Here’s a breakdown of what happens behind the scenes with our Dynamic Weighted Harmonic Mean DAX formula:

1. User Preferences as Weights: Each slicer returns a user-selected value: -1 for low preference, 0 for neutral preference, or +1 for high preference. These values are converted into weights that influence the ranking of candies.
2. Balancing Neutral Preferences: When a neutral value (0) is selected for sugar or price, the formula calculates the distance from the median value. This ensures that middle-ground candies—those not too sweet or too expensive—are prioritized.
3. Randomization for Popularity: If the user selects neutral popularity (0), the formula assigns a random high or low popularity value to each candy (because hey, some things are unpredictable).
4. Weighted Harmonic Mean Calculation: For each candy, the weighted harmonic mean is calculated by balancing sugar, price, and popularity values and the formula ensures that candies with small values (like low sugar) get higher priority, aligning with Sally’s goal to avoid overly sweet treats.
5. Handling Division Safely: To prevent any division-by-zero errors, the formula adds a small constant where needed.

Here’s a snippet of what a subset of the DAX code does:

SugarValue =

IF(

SugarPref = 0,

1 - ([SugarDeviationScore] / MAXX(ALL('candy-data_FACT'), 'candy-data_FACT'[sugarpercent])),

IF(SugarPref = -1, 1 - SELECTEDVALUE('candy-data_FACT'[sugarpercent]), SELECTEDVALUE('candy-data_FACT'[sugarpercent]))

)

This part ensures that when sugar preference is neutral (0), the candy’s sugar value is based on how close it is to the median. If low sugar (-1) is selected, the formula prioritizes candies with lower sugar content.

Enhancing Popularity’s Influence:

At first, I noticed that focusing on low sugar and affordable pricing sometimes caused popularity to get lost in the mix. Suddenly, the top candies were those with the lowest sugar—but not necessarily the ones kids love. That’s when I decided to give popularity a little boost, scaling its weight by 1.5 times to make sure the more beloved candies didn’t fade into the background.

With this tweak, we struck the perfect balance. Reese’s Miniatures and Kit Kat stayed competitive while still keeping low sugar and affordable pricing in mind.

The Final Results:

After the adjustments, the results reflected a perfect mix: candies that were healthy (well, as healthy as Halloween candy gets), affordable, and still super popular with the trick-or-treat crowd. Even Sally had to admit, “You know what? I think the kids might actually enjoy these.”

In the end, this dynamic, flexible approach ensured that candy selection could be customized based on the individual preferences of users like Sally—balancing popularity, sugar content, and price to create the ultimate Halloween candy list. And honestly, if it’s good enough for Sally, it’s good enough for anyone!