As an Excel person I truly appreciate you explaining how this is accomplished in Excel, then kicking it over to Power Pivot to accomplish the same goal. The bite sized learning bit really helps with understanding Power Query. Thanks!
Thank You So Much Mike you are an excellent teacher you maintain speed and accuracy you make complicated stuff so easy to understand and conceptualize thank you so much may God bless you!!
Nice video! More profound than meets the eye! Lessons learnt for me are: - terminology: you have a “double star schema” (in my words) because you have two fact tables - you call it just a star schema. - this example shows the advantage of the data model: this solution has way simpler formulas than even the Excel Classic solution (one might not expect that from DAX). - your example is deceivingly simple, but it works because the granularity of your report is less than or equal to that of your fact tables, AND the DM pivot table takes care of the difference in the grain when aggregating - and you can still throw around the dimension tables to slice however you want (provided it are shared dTables between the fTables). If you use a non-shared dTable, it won’t work. - you could never do this as simple as this in Excel Classic. Thanks for the lesson!
You are welcome, Geert! I like the double Start Schema term. I do not know what the official term is for this sort of structure... "the granularity of your report is less than or equal to that of your fact tables" - yes, we can go year (bigger than month), but day causes problems...
I do not know either. The only thing I can think of is that they are ignorant of the balance of rights and obligations. Unless they are doing similar tasks to try and provide free education to the word AND there is something wrong with the content, they do not have the right to click thumbs down. They may think they have the right of freedom of speech, but when they click Thumbs Down, they only blemish themselves and make the world a worse place...
Great class, Mike! Perfect timing, too, as I’m about to build data models for my organization’s monthly financial reporting and had just begun to think through how to model actual v. budget in data tables extracted from Excel exports from QuickBooks. I had a hunch that I would prefer Power Pivot. This video shows me how. Thanks. Now, all I have to do is figure out how to transform QuickBooks account hierarchies & class tags in the data model.
Glad the class helps, Jim! Thanks for the support with your comment, Thumbs Ups and your Sub : ) As for the hierarchies, what is the situation and how do you need to transform them? Sounds like the perfect job for Power Query.
I agree that it’s a task for PQ. From the organization that I’m helping, I get a monthly Excel file of standard QuickBooks reports: balance sheet; income statement; budgeted income; chart of accounts. My goal is to convert the worksheets into proper data tables, so I need to remove header, blank & total rows and unpivot the monthly numbers, then load to Power Pivot for data modeling & analysis. The challenge that I must tackle is: the QB worksheets put the account hierarchy in separate columns (Level 1 in Column B, Level 2 in Column C, etc.). So, after removing header, blank & total rows, I need to consolidate the account # columns into a single column & tag account #s with their level in the hierarchy. I’m looking forward to figuring it out. Any/all tips are appreciated!
@@jimfitch That is complicated. I have not done something exactly like that. Since Quickbooks has an underlying database, can you ask the administrator to get you proper data sets. Quickbooks also can make custom reports that might be closer to a proper dataset...
Thanks for the suggestion, Mike. It's a good one, but not feasible in this case (not-for-profit organization, part-time bookkeeper with limited understanding of this part of QB, other considerations). I'm going to keep chipping away at it. I expect to learn a lot about PQ & PP and hone my skills with this task.
My initial approach is: In PQ, in the budget & actual fact tables, remove all rows except data rows (done), then calculate a single column that assigns the lowest-level account # as the "surviving" account #, then remove the separate account # columns, then unpivot the data, and create a calendar dimension table. Then, in PQ &/or PP, extract the chart of accounts table & transform it to define the account hierarchy. (My plan for this is fuzzy at this point.) Then, in PQ, set up relationships, measures, etc., and use the account hierarchy to summarize & drill down as needed to display the desired reports.
Hi Mike - thanks for the great video. A follow up question would be regarding the Customer. I too capture actuals at a lower level than the budget and would like to display this all within the same pivot table. Is that possible? When I pull in Customer, it kills the budget because it is not joined at the level. I want it to ignore the budget and only display the actuals.
Excellent mike. I have a question may be you can solve through power pivot. If I have 2 database with database 1 has 500 nb. Of entries and database 2 had 300. Data 1 & 2 has identical header e.g date, equipment type (5) location (5), and amount. The question is how can find these items in data 2 but must match in front of all rows and columns if match found to have a dynamic report. Without using match index vlookup cause those lookup shows where my first data lookup match in other columns & row number but not exactly in front of my first data if matched. Thanks
Very nice video.. What if I want to add a Score to the '%Var' i.e. If %Var > 10%, give it a score of 5, if >5% then 4, if 0% then 3, if less than 5% then 2 and lastly, if less than 10% then a score of 1. I would really appreciate if you could help me, the business whats to assign scores to the variances too.
I have too many other videos in the planning stage, but in the MECS class coming up, I will try to cover those in the DAX video. In many of my earlier DAX video, I used: IF(HASONEVALUE(), VALUES(), ), But SELECTEDVALUE amkes it easier with: An equivalent expression for SELECTEDVALUE(, ) is IF(HASONEVALUE(), VALUES(), ) I have covered ADDCOLUMNS in earlier videos, but whereas, ADDCOLUMNS creates a new column and adds column to table, SELECTCOLUMNS starts with an empty table before adding columns.
I have over 100 DAX videos. Here is the best playlist I have, videos #18 and after are mostly about DAX: ru-vid.com/group/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1
@@bimalpatel7758 I will never do Tableau. Why? I know many have to do it to get a job. But poor companies that use Tableau... Power BI is so much better.
Hi Mike... I`m facing some problem to show this information (Budget vs Actual) in graphs.... could you give me an example how do you present the same table information in a graph? Thanks a lot....
Dear Mike, thank you for your videos! I am trying to model some 1.5 million rows of data, I have some data cleaning in PQ (pivot and unpivot columns, calculating additional columns etc.) but ultimately the calculation is slow and the data doesn't get imported in the data model .. It works up to 500 000 rows. Is that due to computer capacity? Generally, 1.5 million rows shouldn't be a problem.. Thanks
No errors. It loads up to 500 000 if I initially decrease the data set. Otherwise it just keeps calculating and it doesn't load anything. I let it calculate overnight and it didn't work .. :(
