Tag Archives: relationship

  • Comparing Similar Products (Movies) with Power BI

    Back Story: Back in the day, I used to work for this company with a truly all-star / amazing team. We were all part of this company called 20Th Century Fox, and we were in charge of the Theatrical distribution of films in Central America.

    Business Intelligence wasn’t a standard in the industry – at least not how we know BI nowadays, and I was the main person leading the charge of using BI tools to analyze the sales aspect of the business – specifically the Box Office. And that’s how I found PowerPivot (without the spaces back then) and the DAX language. I went deep into it and even took the online Power Pivot Workshop that Marco & Alberto used to deliver back in December of 2011 .

    I was able to implement a WHOLE bunch of reports, analysis and dashboards thanks to Power Pivot, but one thing that I couldn’t implement back then (because I started the journey with my own company), was a Comparison of “Similar” movies for the release planning of an upcoming movie.

    See…before a film gets to a certain theater / cinema / studio / location, it has to go through a certain planning phase. It starts with an estimate on how much that movie might be able to make, but….how can we tell?

    Well, you had a few options, but in the end it was all a manual approach where you’d need to think about what movies might be similar to this one (from every single perspective that you can imagine). Then you’d go through the system and search for every single similar movie one by one so you can grab what was the total gross revenue for each.

    What if…you had a table for all the similar movies (usually called Comps) and you could simply select the Movie that you’re interested in and it’ll display all of the Comps?

    Of course, this doesn’t only apply to movies, but to any product / service that you’d like to compare against similar products.

    This is where this blog post comes in!

  • Understanding Tables, Relationships and Data Modeling in Powerpivot

    image

    Figure 1: Shows a fact table with 2 dimension tables for Customers and SalesPerson

    My response to the image above 2 years ago as an excel user: “What the heck is a FACT and DIM table?Disappointed smile

    One of the common questions that anyone using Powerpivot has is… How should I model my Data in Powerpivot? and it’s not rocket science… it’s easy to learn and hopefully you’ll understand more about it after you finish reading this post.

    If you are new to Powerpivot and new to database modeling then the diagram above makes little to no sense but just think about them as lookup tables that you’ll later be doing some VLOOKUPS, MATCH/INDEX and all those formulas that we, as excel users, know about.

    Let’s learn more about what you should consider when building your Data Model!

  • Cool trick: Always show Yesterday’s, Today’s or Tomorrow’s

    pic1

                                                 Executive: Are these values correct?

                                                Excel-guy: yes, but you need to check the dates

                                                                 slicers to see what dates the report is using

                                              Executive: Ugh… I just want to click on the report and

                                                                       see the latest values

    If you ever had this situation before let me tell you that you’re not alone on that one…I’ve been there before and it’s time to give you some cool easy tricks on how to set up a Powerpivot report that shows you the yesterday, todays, tomorrow, next week or any type of timeframe  (forecasting or that sort of scenario).