Download Excel Start Files: http://bit.ly/2Qf5h6K
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs
In this video learn how to to lookup a discounted price based on Product Name and Units Discount. See how to use 2 VLOOKUP Functions & the ROUND Function in a single formula.
In this video, you will learn about using data validation and drop-down lists.
It covers the following topics:
— Data Validation Criteria
— Input Message
— Customizing Error Messages
— Circle Invalid Entries
— Advanced Data Validation Tricks (such as Create Dependent Validation, Make Sub Headings in Validation, Disguise Numbers as Text, Creating Dynamic Drop Down List)
This video is a part of my Free online Excel training where I show you how to use Excel from the basics and also cover a lot of advanced topics.
You can find more details about this Free Excel Training + the download files here: http://bit.ly/2snelIX
You can also access the Full Excel Training on YouTube here: https://www.youtube.com/watch?v=UPABcYhugZk&list=PLm8I8moAHiH2kkq0S9XGvIbPODrHzXRp6
-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:
-~-~~-~~~-~~-~-
Let’s Connect:
Google+ ► http://bit.ly/2jWiOzU
Facebook ► http://bit.ly/2j9gyrO
Twitter ► https://twitter.com/TrumpExcel
Pinterest ► http://bit.ly/2jWm2mZ
Youtube Channel ► https://www.youtube.com/c/trumpexcel
Download files:
Start File: http://bit.ly/2w4SgDh
Finished File: http://bit.ly/2Q6ESYH
pdf notes for Video #24: http://bit.ly/2w7crRe
In this video learn about how different DAX Data Types can affect multiplication and division results and yield different answers depending on the order of the multiplication or division. This apparently violates the Commutative Property of Multiplication.
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Comparing periods is something you should always do as it adds context to your data, but how do
you calculate Same period last month? PREVIOUSMONTH function will give you the entire previous month, so how do you do it?
#powerbi #curbal #dax #daxfridays
Here is one of the many ways to do it.
Get Northwind Dataset: https://www.youtube.com/watch?v=k3NMIlLffrU
Link to EXCEL SURVEY: https://1drv.ms/xs/s!Ar8CDNp8cGTcgjaHonN82T8I1jQT
Here you can download all the pbix files: http://bit.ly/2PUURaz
SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
http://bit.ly/2V15a0p
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/2qhiZvU
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
http://bit.ly/2V3o0Ux
Many thanks in advance!Here you can download all the pbix files: http://bit.ly/2PUURaz
▲▲▲▲▲▲▲▲▲▲
Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l
************
What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!
https://amzn.to/2YRfIBm
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB
#daxfridays #curbal #dax #powerbi #mvp
All 9 files for video in this zipped folder: http://bit.ly/2JGTGfd
Download file individually at class web site: http://bit.ly/2GF25Ni
pfd notes for Video #23: http://bit.ly/2JGaCTb
In this video learn about how to convert 2 Fact Tables into one with DAX, Worksheet Formulas or Power Query
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Topics:
1. (00:16) Introduction
2. (02:35) Excel Worksheet Formula Solution
3. (05:01) How do we allocate Discount from Invoice Grain to Invoice Line Grain?
4. (05:45) Worksheet Formula for Total Invoice Sales at Invoice Grain using SUMPRODUCT function
5. (09:23) Worksheet Formula for % Sales Discount at Invoice Grain using division
6. (10:00) Worksheet Formula for Line Discount at Invoice Line Grain using VLOOKUP and multiplication
7. (12:12) How do we allocate Shipping from Invoice Grain to Invoice Line Grain?
8. (13:27) Worksheet Formula for Invoice Line Shipping Weight at Invoice Line Grain using VLOOKUP and multiplication
9. (14:30) Worksheet Formula for Invoice Weight at Invoice Grain using SUMIFS
10. (15:04) Worksheet Formula for Line Shipping at Invoice Line Grain using VLOOKUP and multiplication
11. (16:50) Create Excel Reports at Product Grain.
12. (17:03) Standard PivotTable Report
13. (17:53) Worksheet Formula Report
14. (21:26) DAX Formula Solution in Power Pivot
15. (22:30) Look at Data Model and preview of DAX Formulas and functions SUMX, RELATED and RELATEDTABLE
16. (24:30) Bring Excel Tables into Data Model
17. (25:15) Create Relationships between tables
18. (26:23) How to Allocate Invoice Grain Numbers to Invoice Line Grain Numbers
19. (26:56) DAX Formula for Total Invoice Sales at Invoice Grain using SUMX and RELATEDTABLE functions
20. (29:40) DAX Formula for % Sales Discount at Invoice Grain using DIVIDE function
21. (30:50) DAX Formula for Line Discount at Invoice Line Grain using RELATED function and multiplication
22. (31:57) DAX Measure for Total Discount
23. (32:39) Data Model PivotTable Report for Product Discount
24. (33:08) How do we allocate Shipping from Invoice Grain to Invoice Line Grain?+
25. (34:22) DAX Formula for Invoice Weight at Invoice Grain using SUMX, RELATEDTABLE and RELATED.
26. (35:56) Visuals to understand how DAX Formula with SUMX, REALTEDTABLE and RELATED are working to traverse multiple relationships in one formula. This helps illustrates the Power of DAX for Business Calculations.
27. (36:52) DAX Formula for Line Shipping at Invoice Line Grain using RELATED and multiplication and division. Three RELATED function in one formula
28. (39:03) DAX Measure for Total Shipping
29. (39:25) Final Data Model PivotTable
30. (40:22) Power Query Solution in Power BI Desktop
31. (41:09) Why Two Fact Tables will not work with all Dimension Tables for Reporting.
32. (43:05) Summary and visuals of steps we need to perform
33. (44:12) Create blank Power BI Desktop file
34. (44:40) Import Two Fact Table Data Model from Power Pivot
35. (45:40) Power Query Formula to calculate Sales at Invoice Line Grain using Table.AddColumn function
36. (46:53) Power Query Merge to lookup Product Weight at Invoice Line Grain
37. (47:26) Power Query Formula to calculate Product Shipping Weight at Invoice Line Grain using Table.AddColumn function
38. (47:42) Power Query Group By feature to aggregate Invoice Sales, Invoice Shipping Weight and all rows in Invoice Line Grain Table for each Invoice Number.
39. (49:52) Power Query Merge to pull Invoice Grain Shipping & Discount numbers, as well as to pull the Invoice Level Dimensions of Date and Sales Rep ID into the current step in the query (later after expanding it will be the Invoice Line Grain).
40. (50:52) Power Query Formula for % Sales Discount at Invoice Grain using Table.AddColumn function
41. (52:11) Expand to get back to Invoice Line Grain
42. (52:31) Note about Unit Price and how it is stored as a Fact because it changes so often.
43. (54:16) Power Query Formula for Line Discount at Invoice Line Grain using Table.AddColumn function and Number.Round
44. (54:49) Power Query Formula for Line Shipping at Invoice Line Grain using Custom Column using Table.AddColumn function and Number.Round
45. (57:23) Remove all column we do not need in final Fact Table
46. (58:08) Load Tables to Data Model, except Invoice Level Table.
47. (59:15) Create DAX Measures for Shipping, Discounts and Sales
48. (01:00:10) Create % DAX Measures for Shipping and Discount as a percent of sales. Use the DIVIDE DAX Function.
49. (01:01:15) Hide Columns from Report View
50. (01:01:33) Look at Final Data Model
51. (01:01:47) Create Visualization in Power BI Desktop
52. (01:04:40) Summary
You know how this works by now right? If I learn something useful, I share it here with you and in today’s video I am going to share my top 5 power bi tips to fix current issues and cool functionality.
Did I manage to surprise you with a tip you didnt know? #curbal #powerbi #5tips
Keynotes:
1. Word wrap, mini map and more power query
2. Copy formatting in a visual
3. Fix non-responsive/ freeze power bi ribbon
4. Bookmark filter pane status
5. Copy buttons with actions.
Here you can download all the pbix files: http://bit.ly/2PUURaz
SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1
Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
http://bit.ly/2V15a0p
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/2qhiZvU
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
http://bit.ly/2V3o0Ux
Many thanks in advance!
▲▲▲▲▲▲▲▲▲▲
************
What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!
https://amzn.to/2YRfIBm
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB
#POWERBITIPS #CURBAL #POWERBI #MVP
In this video of this online Excel Training, you will learn about using Named Ranges in Excel.
It covers the following topics:
— Creating Named Ranges
— Managing Named Ranges
— Important Keyboard Shortcuts
— Creating Dynamic Named Ranges using OFFSET and INDEX formulas
This video is a part of my Free Excel course where I show you how to use Excel from the basics and also cover a lot of advanced topics.
You can find more details about this Free Excel Training + the download files here: http://bit.ly/2snelIX
You can also access the Full Excel Training on YouTube here: https://www.youtube.com/watch?v=UPABcYhugZk&list=PLm8I8moAHiH2kkq0S9XGvIbPODrHzXRp6
-~-~~-~~~-~~-~-
Find Amazing Excel Tips & Tutorials:
TrumpExcel.com | Online Excel Tips & Tutorials (with videos)
-~-~~-~~~-~~-~-
Let’s Connect:
Google+ ► http://bit.ly/2jWiOzU
Facebook ► http://bit.ly/2j9gyrO
Twitter ► https://twitter.com/TrumpExcel
Pinterest ► http://bit.ly/2jWm2mZ
Youtube Channel ► https://www.youtube.com/c/trumpexcel
To buy or try WBMerge: http://bit.ly/2WH82Qt
Save 20% on WBMerge through June 30 2019 using discount code MREXCEL
In this video, I try the WBMerge utility on a collection of Excel workbooks that might be problematic in Power Query.
WBMerge is a $39 utility from Workbook Tools. The utility can merge data from multiple Excel workbook or multiple Google Sheets files.
In today’s video I am going to show you how to export data from one table or the entire model using DAX Studio.
#curbal #dax #daxstudio
Keynote:
02:00 Download data from one table with DAX Studio
02:40 Download the entire model with DAX Studio
Here you can download all the pbix files: http://bit.ly/2PUURaz
SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1
Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
http://bit.ly/2V15a0p
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/2qhiZvU
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
http://bit.ly/2V3o0Ux
Many thanks in advance!
▲▲▲▲▲▲▲▲▲▲
************
What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!
https://amzn.to/2YRfIBm
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB
To be able to optimize DAX queries, you need to know how the formula engine and the storage engine work with them, so how about we let them explain themselves?
#curbal #powerbi #dax #DAXFridays
Hope you enjoy the video, i had fun doing it 🙂
Have you missed the Vertipaq series? Here are the videos:
Part1: In this video, I will go through the main known things that Vertipaq does to compress the data: https://www.youtube.com/watch?v=b8oSgTOeYCE
Part2: In this video, we will go through what it means that Vertipaq is a columnar database and why performance is so good: https://www.youtube.com/watch?v=A461ktvExH0
Part3: Why is Vertipaq so fast? Segmenting the data is one of Vertipaq tricks to manage data at blazing fast speeds. In this video, I will explain what that is: https://www.youtube.com/watch?v=A67tsJ03Afs
Get Northwind Dataset: https://www.youtube.com/watch?v=k3NMIlLffrU
Link to EXCEL SURVEY: https://1drv.ms/xs/s!Ar8CDNp8cGTcgjaHonN82T8I1jQT
Here you can download all the pbix files: http://bit.ly/2PUURaz
SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
http://bit.ly/2V15a0p
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/2qhiZvU
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
http://bit.ly/2V3o0Ux
Many thanks in advance!Here you can download all the pbix files: http://bit.ly/2PUURaz
▲▲▲▲▲▲▲▲▲▲
Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l
************
What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!
https://amzn.to/2YRfIBm
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB
#daxfridays #curbal #dax #powerbi #mvp
DAX Fridays! #4: COUNT, COUNTA, COUNTX, COUNTAX, DISTINCTCOUNT AND COUNTROWS in DAX
Let’s go through all the count expressions available in DAX!
Here you have the keynotes for the video so you can jump to the function you want to learn:
02:44 COUNT text (#ERROR)
03:24 COUNT numbers
04:20 COUNT blanks
04:56 COUNT dates
05:36 COUNTA text
06:44 COUNTA numbers
07:27 COUNTX
09:16 COUNTAX
09:58 DISTINCTCOUNT
10:59 COUNTROWS(DISTINCT)
12:06 COUNTBLANK
12:34 COUNTROWS
13:32 COUNTROWS with columns (#ERROR)
14:33 COUNTROWS with an expression
Link to Curbal Glossary: http://bit.ly/2JbxKt8
PREVIOUS VIDEO: https://www.youtube.com/watch?v=vcYz0XPklXg
NEXT VIDEO: https://www.youtube.com/watch?v=-oDpOfhgmzA
Dowload the sample file here:
http://bit.ly/2Jv3RDN
Looking for a download file? Go to our Download Center: http://bit.ly/2PUURaz
SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1
Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l
ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: http://bit.ly/2qhiZvU
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
http://bit.ly/2V3o0Ux
Many thanks in advance!
▲▲▲▲▲▲▲▲▲▲
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo
► Google +: https://goo.gl/rvIBDP
► Facebook: https://goo.gl/bME2sB
► Linkedin: https://goo.gl/3VW6Ky
#DAXFRIDAYS #CURBAL #DAX #POWERBI #MVP
Cross Selling Matrix Deep Dive – Power BI & DAX Tutorial
In this tutorial we cover how to create a cross selling matrix.
This requires a full understanding of the concept of ‘context’ and much more.
Some advanced DAX formulas are covered in detail, especially table functions.
Plenty of great techniques to learn about during this one.
Enjoy!
Sam
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – http://bit.ly/2SGof6C
FREE COURSE – Ultimate Beginners Guide To DAX – http://bit.ly/2AyEb3P
FREE – Power BI Resources – http://bit.ly/2C2aBn3
FREE – 60 Page DAX Reference Guide Download – http://bit.ly/2AyEc7T
Learn more about Enterprise DNA – http://bit.ly/2RacRD3
Enterprise DNA Membership – http://bit.ly/2AAqa5u