Microsoft Excel Tutorial: Create an Excel Pivot Table to Identify Which Customers Are Paying Less than Average for a Product.
To download the workbook from today: https://www.mrexcel.com/youtube/4XrKZhiBB-k/
Hey Excel enthusiasts! 👋 In this MrExcel netcast, we tackle a fascinating question from Glenn, who joined our recent Nashville IMA webinar. Ever wondered which customers are paying less than the average? 🧐 We’ve got you covered!
🔍 Using a special pivot table based on the Data Model, we delve into the powerful DAX function CALCULATE. Follow along as we transform our data into a table named “Sales” and explore 12,000 rows of last year’s data with 20 customers and two products.
💡 Learn how to create a weighted average using DAX, and witness the magic of adding measures like “average price” for specific customers and products. Discover the ability of DAX to unapply filters, allowing us to calculate averages within the pivot table dynamically.
🚀 But the real gem lies in identifying customers paying less than the average. Watch as we employ a clever trick to filter the pivot table dynamically, revealing insights on customers getting a sweet deal. Glenn, this one’s for you!
👍 If you’re into Excel wizardry, don’t forget to hit the like button below, and subscribe for more insightful tutorials. Ring the bell to stay updated! Have questions or comments? Drop them below, and let’s excel together! 🚀💼
Buy Bill Jelen’s latest Excel book: https://www.mrexcel.com/products/latest/
Table of Contents
(0:00) Clicking Like helps the algorithm
(0:14) Problem Statement: Which customers paying less than average?
(0:44) Ctrl+T table
(1:07) Add to Data Model
(1:46) Average Price from Pivot Table Totals
(4:33) DAX CALCULATE Function with ALL
(6:17) Average price for all customers
(7:15) This customer’s price compared to average
(8:24) Removing intermediate calculations
(9:10) Magic Cell to Filter a Pivot Table
(9:39) Wrap-up
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #pivottable #pivot_table #excelpivot #excelpivottablestutorial #powerpivot #DAX #excelformula #getpivotdata #pivotchart
This video answers these common search terms:
excel data model weighted average
dax function calculate tutorial
pivot table with data model in excel
analyzing customer payments in excel
excel dynamic filtering in pivot table
average price calculation in excel
excel tutorial on customer pricing
data analysis with excel measures
excel tips for uncovering hidden insights