Budget Vs. Actual Variance Analysis: Data Modeling, DAX, or Worksheet Formulas? 365 MECS Class 19

This video shows how to create an actual vs. budget variance report in three ways: 1) Relationships and DAX Formulas, TREATAS Dax Function and Worksheet Formulas.

This video covers.
1. (00:00) Introduction and video topics
2. (00:32) Budget Vs Actual Grain Problem
3. (01:13) Two Fact Tables
4. (02:20) Create Budget Vs Actual Variance Report with three methods
5. (02:20) Worksheet Formulas to Create Budget Vs Actual Variance Report
6. (05:41) Relationships and DAX Formulas to Create Budget Vs Actual Variance Report
7. (10:45) TREATAS function to simulate a relationship to create a Budget Vs Actual Variance Report
8. (14:06) Video Summary and Conclusions
9. (14:38) Closing and Video Links


#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #datamodel #DAX #budget #varianceanalysis #variance #relationships

[canirmalchoudhary]

It's awesome to learn data modeling

[paulsingleton]

Awesome video Mike, especially liked the double negative trick. Thank you very much.

[jameszhou]

Thanks Master Mike! In fact, THEATAS is like those temporary table used in SQL for temporary analysis purpose. very good stuff.

[vishal.pandey]

Data analysis is fun using excel when power tools are more effective and time saving

[kiwikiow]

I always learn something new from your video, TREATAS. Thank you Mike :)

[Henrik.Vestergaard]

Freaking awesome 👊 Love that you addded the TREATAS/INTERSECT

[GeertDelmulle]

Thanks, Mike, learned something new with TREATAS for creating virtual relationships.
Good stuff, great video! :-)

[ExcelInstructor]

Hi Mike,
I love this series truly!
Probobly i will (finaly) start using DAX not only learn it :)

[williamarthur]

Thanks, never got around to using TREATAS before, my first thoughts were to USERELATIONSHIP, so nice surprise, I think I'll have to go over it a few times, as with a lot of DAX and M.

[jerrydellasala]

Great lesson - AS ALWAYS!
For the Power BI samples, the Excel file name isn't correct, but changing the Source to either of the 19-M365ExcelClass*.xlsx files seems to work fine.

[nikakalichava]

Hey Mike!
Question: You could have made a relationship in Power BI within Budget and two other dimension tables and the outcome would have been the same... right? Instead of using TREATAS it would have just been SUM... right?

[gvitullib]

Hi Mike, another amazing video, thanks dude. I'm testing the alternative BudgetedSales($)NoTREATAS (INTERSECT) measure, and if I´m not wrong, it seems to compute an approximate value not 100% equals to the BudgetedSales($)TREATAS measure. Is this something expected to occur?

[ExcelInstructor]

also, Mike, Can I ask a question about Video editing?
Cos thats why i got stuck with my yt channel...

[ricos]

Nice video Mike. Fantastic presentation of the three methods. I'd probably use the second. Although I dont really have a job at the moment, so I'd only hypothetically use the second method! Good work.

[ahmarkareemi]

I need some help from you I am stuck an formula in excel sheet... I tell you the status of the sheet....A1 to L1 it is 12 month to L13 it is 12 months data.... Now I want the data of these 12 months in one column and in the same A16 is the month heading.... In the cells from A17 to A27 for 12 of month data.... I need 12 months of data show just chang the Heading Is it possible?

[mgrnmgrn]

Great channel to learn excel and its tricks, your effort is great service to human hats off to Mr.Mike.Please share your Email id .