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...
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.
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...
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
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 .. :(
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: ua-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html
@@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.
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!
You are welcome, Laura!!!
hello mike. i did not use the date table, instead usedmy own calendat table which you taught us earlier .....and it worked just fine. 🥰😍 thank you
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!!
You are welcome for the maintained speed and accuracy, Girish!!!
Mike you make the work look so easy
It is when you are having fun ; )
your work is clear, great, and organized very well. All ur videos are worth seeing as they are with the file in question downloadable.
again....I am soo thankful for your generosity in making your teaching available for all....I've learned so much from your videos. THANK YOU!
You are welcome, Aeri!!! Thank you, for your support on each video that you watch : )
As usual, perfect explanation and great example from the real life.
Many thanks, Mike.
You are welcome, O Masterful Power Query Poet : )
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...
This channel is all about unlimited fun with excel
Unlimited fun - I love that, RRR!!!!
Thank you. That was beautiful.
You are welcome!!!
Clear explanations - appreciate the "watch outs" also.
Glad it is working for you, Jeff!!!!
Amazing as always, thank you Mike!!
You are welcome, Tracy!!!!
Many thanks, Mike. you are really offering a great help to the community
Glad you think so!
Thanks Mike.. another great tutorial from the Grand Master! Thumbs up!!
Thanks, Wayne!
Thanks Mike for the video. As always it is very helpful.
You have always the way to amaze us!! Thank you Mike a million times!!! :)
Another great one Mike! The Master strikes again
Glad it is great for you, Chris!
Powerful intro music for the most powerful MSPTDA videos! 👊
: ) : )
Mike thank's for sharing. Ever, your numbers are more trustworthy than these comming from customers ! ;-)
You are welcome, Jue!!!
Great explanation! Thanks for showing us all the cool tricks. Please post more videos like this.
Glad you enjoy these, Shabnam!!! There is always lots more to come from excelisfun : )
Thanks Mike for this material. Amazing
You are welcome for the MSPTDA amazing : )
Another practical and awesome example. Thumbs up!
Thanks, Teammate!
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.
Excellent...👍
Thnks for sharing such a nice video..🙏🙏
You are welcome!
An absolutely must for a 2 fact tables example
Glad it is good for you, Amilcar!!!!
I am always amazed when I see "I don't like". how is it possible!!!!
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...
@@excelisfun This tip, alone, has changed my career. Thank you.
@@universalproblemsolver Yes!!!!! I love to hear that. That is why I post : )
It's simple but specific. Thanks a lot, Mike
You are welcome for the simple and specific, Manh!!! Thank you for your support : )
Thank you, fantastic lesson! Thanks so much for sharing
You are welcome so much for the share! Thanks for your support, Sally Lee!
Even better than the last....
More fun in a Data Modeling and DAX way : )
Great as usual ..many thanks Professor
You are welcome, Wajdi!!!!
Waal! Thank you Mike. I was waiting for this the whole week. Amazing dedication.
You are welcome, Daison!!! I hope it was worth the wait : ) Thanks for your support!
Another great one Mike, cheers!
The amazing Mike with EXCELlent video. Thanks for the share.
You are welcome! Thanks for your amazing and consistent support : )
Well done Mike ... Thanks for the tutorial... It's very usefull for me
Glad it helps, Denni : )
Another great video!
Hi Mr. Mike so sorry I was in Holiday vacation so that is why I haven't seen any video of the last 5 videos but i'm working on them now.
No worries! I hope you had a happy holiday!
Any video with Customer attribute in Budget?
Great video!
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.
Great work Mike
Thanks, Dave!
My favorite kinda variance!
Yes, indeed, positive Revenue Variance is great : )
Grazie ❗️🙏🏼 🇮🇹
Thanks Mike
Hi thank you a lot Mike! Following on this, how could I count in each month, how many days have variance within 5%?
Thanks Mike. Great Video. :)
You are welcome, John Borg : ) : )
What will happen if we don't have a dProduct table? Can we still filter by product?
Mike, could you please help us deriving KPI's with different data sets we receive sometimes it is product sales or marketing
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
Thanks a stack, very useful indeed.
If I have a specific query, where is the most appropriate place to send my query and examples?
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....
Aaaaammaaaazzziiiiinnnnnggggggg!!!!
Yes!!!!!! I love to hear that! Thanks for stopping by in the comments, K9Syndicate!!!!
Awesome, Thank you
Nice
Glad it is nice for you, Qasr!!!
Great video
Thanks, Laza : )
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
I do not know what the problem is. 1.5 million rows should not be an issue.
does it give an error or it just silently only loads 500,000 rows? thats very weird.
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 .. :(
Amazing
Glad you liked it, Luke : )
well done!
Thank you so much.
You are welcome, Ogwal!
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.
❤️👌
Awesome!
Glad it is awesome for you, Birendra : )
Sir can you please make video on selectedvalue and selectedcolum daxx
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.
@@excelisfun ok sir but keep making video on dax as other people are not explaining as you do hats of to you
I have over 100 DAX videos. Here is the best playlist I have, videos #18 and after are mostly about DAX:
ua-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html
Thank you ......russo making confuse and you making clear ....... Also sir tableau is in market pls have video in tableau
@@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.
top
As you said a lot of fun a lot of khowledge
Yes, indeed, Mohamed!!!!
No one match you
Glad it all helps!!!