The Cost of a Healthy Diet: Fruits and Vegetables

The Cost of A Healthy Diet: Fruits and Vegetables

Analyzing the cost of meeting USDA-recommended servings of fruits and vegetables, with tools to help estimate grocery expenses.

Goal

This project aims to analyze trends in fruit and vegetable prices while also providing tools to help users estimate the cost of meeting USDA-recommended daily servings of fruits and vegetables.

Data

The data for this project is sourced from the United States Department of Agriculture (USDA), which provides comprehensive guidelines on recommended daily servings of fruits and vegetables, along with price data for various food items.

Approaches Used

Data Cleaning and Processing (R)

Data Visualization (PowerBI)

Quick Navigation

(Click on a section to jump directly to it!)

Building Interactive Cost Calculators – Step-by-step guide to creating daily and weekly cost calculators for fruits and vegetables.

Cost of a Healthy Diet: Fruits – An interactive dashboard exploring fruit prices and a grocery cost calculator for fruit purchases.

Cost of a Healthy Diet: Vegetables – An interactive dashboard analyzing vegetable prices and a grocery cost calculator for vegetables.

Combining Fruits & Vegetables in a Cost Calculator – How to use DAX in Power BI to create a unified daily and weekly produce cost estimator.

Cost of a Healthy Diet: Fruits & Vegetables – An interactive dashboard comparing fruit and vegetable prices, plus a combined grocery cost calculator.

Final Thoughts: Smart Produce Shopping – Insights on balancing cost and nutrition, plus how to use the total cost calculator for grocery planning.

Creating interactive cost calculators for fruits and vegetables:

In this section, I created two separate interactive cost calculators—one for fruits and one for vegetables—each placed on its own dashboard. Users can select multiple items from a dropdown menu, and the respective table will automatically calculate and display the total daily cost, weekly cost, and weekly cost for a family of four, based on the USDA’s recommended daily consumption. Since the calculations for both dashboards follow the same structure, this section covers them together. These tools help users estimate produce expenses separately before combining them in the final cost calculator for a more comprehensive view.

Step 1: Calculating Daily Costs for Produce

The following DAX formula calculates the daily cost of fruits and vegetables based on USDA daily intake recommendations—2 cups for fruits and 2.5 cups for vegetables:

Daily_Cost =

VAR SelectedPrice = SELECTEDVALUE(cropdata[CupEquivalentPrice])

VAR DailyIntake =

SWITCH(

SELECTEDVALUE(cropdata[Category]),

“Fruit”, 2,

“Vegetable”, 2.5,

2 // Default value (fallback)

)

RETURN SelectedPrice * DailyIntake

Step 2: Calculating Average Daily Costs per Category

The purpose of this step is to ensure that when users select multiple fruits or vegetables, the result reflects the average daily cost of the selected items rather than summing them. This allows for a more accurate comparison of produce costs across different selections.

Avg_Daily_Cost_Per_Category =

AVERAGEX(

FILTER(cropdata, NOT(ISBLANK([Daily_Cost]))),

[Daily_Cost]

)

Step 3: Weekly Calculations

Avg_Weekly_Cost_Per_Category = [Avg_Daily_Cost_Per_Category] * 7

Calculating the weekly cost for a family of 4:

Avg_Weekly_Cost_Family_Per_Category = [Avg_Weekly_Cost_Per_Category] * 4

Step 4: Building the Cost Calculators

To create these interactive cost calculators in Power BI, follow these steps:

Add Slicers – Insert slicers for Fruits and Vegetables on their respective dashboards. These slicers allow users to select multiple items, reflecting real-world purchasing behavior.

Create Table Visuals – Add a table visual that includes the following measures:

Avg_Daily_Cost_Per_Category – Calculates the average daily cost of the selected fruits or vegetables.

Avg_Weekly_Cost_Per_Category – Computes the weekly cost by multiplying the daily cost by seven.

Avg_Weekly_Cost_Family_Per_Category – Estimates the weekly cost for a family of four, based on USDA recommendations.

Creating  an interactive cost calculator for fruits and vegetables combined:

In this section, I created an interactive cost calculator that allows users to select multiple fruits and vegetables from a dropdown menu. Once selected, a table automatically calculates and displays the total daily cost, weekly cost, and weekly cost for a family of four, based on the USDA’s recommended daily fruit and vegetable consumption. This tool helps users estimate produce expenses while ensuring they meet USDA guidelines, making it useful for budgeting and meal planning.

Step 1: Creating Separate Disconnected Tables for Fruit and Vegetable Slicers

This step is necessary in order to avoid the fruit and vegetable slicers filtering each other out. We want our calculator to filter for fruits and vegetables separately.

Creating a disconnected fruits table:

FruitSelection = FILTER(CropData, CropData[Category] = “Fruit”)

Creating a disconnected vegetables table:

VeggieSelection = FILTER(CropData, CropData[Category] = “Vegetable”)

Step 2: Daily Cost Calculations

Creating a new measure to calculate daily cost for all selected fruits:

Selected_Fruits_Daily_Cost =

VAR SelectedFruitRows =

FILTER(

cropdata,

cropdata[Category] = “Fruit” && cropdata[Name] IN VALUES(FruitSelection[Name]) – Apply slicer selection for fruits

)

VAR TotalDailyCost =

SUMX(SelectedFruitRows, cropdata[Avg_Daily_Cost_Per_Category]) – Sum of the selected daily costs

VAR RowCount =

COUNTROWS(SelectedFruitRows) – Count of the selected rows

RETURN

IF(RowCount > 0, TotalDailyCost / RowCount, 0) – Return average or 0 if no rows

Creating a new measure to calculate daily cost for all selected vegetables:

Selected_Vegetables_Daily_Cost =

VAR SelectedVeggieRows =

FILTER(

cropdata,

cropdata[Category] = “Vegetable” && cropdata[Name] IN VALUES(VeggieSelection[Name]) – Apply slicer selection for vegetables

)

VAR TotalDailyCost =

SUMX(SelectedVeggieRows, cropdata[Avg_Daily_Cost_Per_Category]) – Sum of the selected daily costs

VAR RowCount =

COUNTROWS(SelectedVeggieRows) – Count of the selected rows

RETURN

IF(RowCount > 0, TotalDailyCost / RowCount, 0) – Return average or 0 if no rows

Creating a new measure to calculate total daily cost for all selected produce (sum of averages for selected fruits and vegetables):

Selected_Total_Daily_Cost =

[Selected_Fruits_Daily_Cost] + [Selected_Vegetables_Daily_Cost]

Step 3: Weekly Cost Calculations

Creating a new measure to calculate weekly cost for all selected fruits:

Selected_Fruits_Weekly_Cost =

VAR SelectedFruitRows =

FILTER(

cropdata,

cropdata[Category] = “Fruit” && cropdata[Name] IN VALUES(FruitSelection[Name]) – Apply slicer selection for fruits

)

VAR TotalWeeklyCost =

SUMX(SelectedFruitRows, cropdata[Avg_Weekly_Cost_Per_Category]) – Sum of the selected weekly costs

VAR RowCount =

COUNTROWS(SelectedFruitRows) – Count of the selected rows

RETURN

IF(RowCount > 0, TotalWeeklyCost / RowCount, 0) – Return average or 0 if no rows

Creating a new measure to calculate weekly cost for all selected vegetables:

Selected_Vegetables_Weekly_Cost =

VAR SelectedVeggieRows =

FILTER(

cropdata,

cropdata[Category] = “Vegetable” && cropdata[Name] IN VALUES(VeggieSelection[Name]) – Apply slicer selection for vegetables

)

VAR TotalWeeklyCost =

SUMX(SelectedVeggieRows, cropdata[Avg_Weekly_Cost_Per_Category]) – Sum of the selected weekly costs

VAR RowCount =

COUNTROWS(SelectedVeggieRows) – Count of the selected rows

RETURN

IF(RowCount > 0, TotalWeeklyCost / RowCount, 0) – Return average or 0 if no rows

Creating a new measure to calculate total weekly cost for all selected produce (sum of averages for selected fruits and vegetables):

Selected_Total_Weekly_Cost =

[Selected_Fruits_Weekly_Cost] + [Selected_Vegetables_Weekly_Cost]

Step 4: Weekly Cost Calculations for a Family of 4

Creating a new measure to calculate weekly cost (for a family of 4) for all selected fruits:

Selected_Fruits_Weekly_Cost_Family =

VAR SelectedFruitRows =

FILTER(

cropdata,

cropdata[Category] = “Fruit” && cropdata[Name] IN VALUES(FruitSelection[Name]) – Apply slicer selection for fruits

)

VAR TotalFamilyCost =

SUMX(SelectedFruitRows, cropdata[Avg_Weekly_Cost_Family_Per_Category]) – Sum of the selected family costs

VAR RowCount =

COUNTROWS(SelectedFruitRows) – Count of the selected rows

RETURN

IF(RowCount > 0, TotalFamilyCost / RowCount, 0) – Return average or 0 if no rows

Creating a new measure to calculate weekly cost (for a family of 4) for all selected vegetables:

Selected_Vegetables_Weekly_Cost_Family =

VAR SelectedVeggieRows =

FILTER(

cropdata,

cropdata[Category] = “Vegetable” && cropdata[Name] IN VALUES(VeggieSelection[Name]) – Apply slicer selection for vegetables

)

VAR TotalFamilyCost =

SUMX(SelectedVeggieRows, cropdata[Avg_Weekly_Cost_Family_Per_Category]) – Sum of the selected family costs

VAR RowCount =

COUNTROWS(SelectedVeggieRows) – Count of the selected rows

RETURN

IF(RowCount > 0, TotalFamilyCost / RowCount, 0) – Return average or 0 if no rows

Creating a new measure to calculate total weekly cost (for a family of 4) for all selected vegetables:

Selected_Total_Weekly_Cost_Family =

[Selected_Fruits_Weekly_Cost_Family] + [Selected_Vegetables_Weekly_Cost_Family]

Notes:

SUMX() is used to sum the selected values for fruits and vegetables.

COUNTROWS() counts the number of selected fruits or vegetables to calculate the average by dividing the sum by the count.

IF(RowCount > 0, … , 0) ensures that if there are no selected fruits or vegetables, it returns 0 instead of an error or invalid result.

The total row (Selected_Total_Daily_Cost, Selected_Total_Weekly_Cost, etc.) sums the averages of the selected fruits and vegetables.

Step 5: Building the Cost Calculator

To create this interactive cost calculator in Power BI, follow these steps:

Add Slicers – Insert slicers for Fruits and Vegetables using the FruitSelection and VegetableSelection tables. These slicers allow users to select multiple items, reflecting real-world purchasing behavior.

Create Table Visuals – Build three separate table visuals to display the cost calculations:

Daily Cost Table: Includes Selected_Fruits_Daily_Cost, Selected_Vegetables_Daily_Cost, and Selected_Total_Daily_Cost.

Weekly Cost Table: Includes measures for weekly costs, calculated based on daily costs multiplied by seven.

Family Cost Table: Includes measures for weekly costs for a family of four, adjusting costs accordingly.

Combine Visuals – Arrange the three tables in Power BI to create a unified view.

When you select items from the slicers, the table will dynamically update to show the averages for the selected fruits and vegetables and the total sum for those averages.

Final Conclusion: Optimizing Produce Choices for Budget & Nutrition

Analyzing fruit and vegetable prices side by side highlights key patterns that can help consumers make cost-effective decisions. Vegetables are generally more affordable than fruits, making them a great option for budget-conscious shoppers. While frozen produce tends to be the most expensive form, it offers convenience and a longer shelf life, whereas canned and dried options provide the best value. Notably, fruit juice is the cheapest fruit form, but no equivalent exists for vegetables, reinforcing the importance of strategic selection.

​

For those looking to balance nutrition and cost, focusing on a mix of fresh, canned, and dried produce while being selective about frozen options can help maximize savings. To put these insights into action, shoppers can use the total cost calculator to estimate their grocery expenses, ensuring they meet USDA-recommended intake while staying within budget.