project flyers
Flyers: Power BI Results
Table of Contents
- Introduction
- Version 1
- Version 2
- Version 3
- Reporting Dashboard
- Data Cleansing
- Power BI Results
MS Fabric (v4)AI with Power BI
This is part seven of a series of short blogs chronicling a pet project I’ve come to know as Flyers. Flyers is a data exploration journey to capture authentic sale prices from local grocery stores and use that data to visualize trends over time. Files associated with this project are available at https://github.com/carsonruebel/Flyers. This article covers the initial exploration of data, some additional cleanup steps, and creation of measures.
Once enough data exists to begin to see products being on sale multiple times, I find that simply playing with the basic slicers to see what the data looks like is a great jumping off point for building additional logic to transform data into information. Within minutes I had identified the following issues and desires.
- Desire to view prices for a specific product/merchant over time
- Desire to see how often a specific product is on sale
- I was seeing sales with a price of $0.00
View Prices over Time
- Created date table (https://www.youtube.com/watch?v=WybnTHDl-AM)
- Note that using date table needs to sort by num column for qtr, month, monthyear (https://www.youtube.com/watch?v=NlX7Cp2P0d4&t=3s)
- MOM calculation (& associated %)
- Highest price minus lowest price (& associated %)
- Most recent price minus earliest price (& associated %)
View Frequency of Products
- Count of distinct start dates
- Count of days on sale
- 2x measures (these two can/should ignore baddataflag — everything else probably wants it on)
Organizing Measures
Haven’t even done this yet… Need to put it in the new Semantic Model
Issue: Price of $0.00
Deleted in Power Query — do it as early up the chain as possible, but want to keep the data for future potential analysis.
Re-Fixing DIM_Product ETL Issue
After building the time intelligence measures I was able to identify a further issue. From looking at items that had the largest price variance, I found two instances where a product showed two different sales prices for the same day and merchant. This was very reminiscent of the bad data issue we fixed before, and indeed when looking back at my fix, I did not account for a merchant offering the same item using the same sale name on the same date but having two different prices (presumably two different quantities). This is a fairly niche case, there were only 26 incidents of it in the server. I wrote the following to fix the issue.
INSERT CODE AND STUFF (unfortunately I didn’t capture any screenshots)
Putting it to use
Use this to show the worst inflation offenders
show outliers
use badflag to filter on and off (visual element?)
Findings
- look at ribeye (stay roughly same)
- look at pete & gerry organic eggs (increase)
- Wild-Caught Columbia River King Salmon Fillets (increase)
- look at sunkist chunk light tuna (stay same)
- qfc organic honeycrisp went down (ignore 2lb bags)
- kerrygold cheddar cheese went up (10%)
- Danby 24" Built-in Dishwasher in Stainless Steel DDW2404EBSS big increase
Make this a new article afterwards?
speaking of visual elements, do I want to have another blog about making report visually snazzy? combine with this one? combine with previous dashboard one?