Download Excel File: https://ift.tt/2GpZGqw
In this video learn how to count the number of 4.0 grades for each test. The complication is that as we copy the formula down a column, we need the range to move to the next column. Learn how to lookup a column using the INDEX and MATCH Functions, that use that range in the COUNTIFS function. Also see two bonus formulas that use the RIGHT Function and the ROWS function.
Tag: How to
AI in Power BI #1: Anomaly detection API
In today’s video, I will test the Anomaly detection API in Power Bi desktop and we will visualize the data too 🙂 @Msdev_WE
#powerbi #curbal #ai
Here are the links promised on the video:
https://ift.tt/2Z8y8NC
https://ift.tt/2GtAU8G to connect to the cognitive services.
The Anomaly Detector API enables you to monitor and detect abnormalities in your time series data with machine learning. The Anomaly Detector API adapts by automatically identifying and applying the best-fitting models to your data, regardless of industry, scenario, or data volume. Using your time series data, the API determines boundaries for anomaly detection, expected values, and which data points are anomalies.
Here you can download all the pbix files: https://ift.tt/2yGx9Ih
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:
https://ift.tt/2NEZM2b
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: https://ift.tt/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:
https://ift.tt/2NrgChB
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://ift.tt/2I8nl09
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB
How And When To Use COMBINEVALUES In Power BI – DAX Function Review
In this video we will be covering how and when to use the COMBINEVALUES DAX function in Power BI.
This is not a difficult function to use and is similar to the CONCANTENATE function in excel.
I run through a number of examples to give you a good idea of where this can be used effectively within your development.
Sam
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – https://ift.tt/2Lmxubn
FREE COURSE – Ultimate Beginners Guide To DAX – https://ift.tt/2IJLvOv
FREE – Power BI Resources – https://ift.tt/2LiAVQj
FREE – 60 Page DAX Reference Guide Download – https://ift.tt/2IJLx95
Learn more about Enterprise DNA – https://ift.tt/2LiAWDR
Enterprise DNA Membership – https://ift.tt/2IJLxWD
Excel for Business Analytics Course Trailer. Free YouTube Learning Playlist Class from excelisfun.
Join Free class from excelisfun at YouTube: https://www.youtube.com/playlist?list=PLrRPvpgDmw0mSJCZaqQPFj0eto4qnzkCZ
Free University Business Analytics Class from the world renowned Excel Educator, Mike Girvin, Microsoft Excel MVP and Highline College Professor.
76 videos, free notes, Excel files make this THE best free online class for learning how to use Excel to do all of your University Analytics Tasks. This class uses the amazing new YouTube Learning Playlist structure. This is the Highline College BI 348 class taught by Michael Girvin.
Topics in Free Class:
1. Building Good Spreadsheet Models
2. What If Analysis
3. Excel Data Analysis Tools
4. Frequency Distributions & Histograms
5. Averages, Variation, Probability & More
6. Power Query for Importing, Cleaning & Transforming Data
7. Excel Power Pivot for Big Data
8. Visualizing Data
9. Linear Regression
10. Time Series Analysis and Forecasting
11. Monte Carlo Simulation
12. Not Just One Way To Analyze a Business Problem
Delete Rows Based on a Cell Value (or Condition) in Excel [With and Without VBA]
This post covers different ways to delete rows based on a cell value in Excel (or delete based on conditions).
The following four methods to delete rows based on cell value are covered in this video:
1) Filtering the data and deleting the rows that match the filter criteria
2) Sorting the rows alphabetically and then deleting the rows that are grouped together and match the criteria
3) Finding the cells that have a specific value and then deleting these rows
4) Using VBA Autofilter to filter and delete rows based on cell value using VBA
The method you choose can depend on how your data is structured,
Note that when you delete a row, it also deletes any content that may be on the right or left of that rows. In case you want to delete the data in a dataset but keep the other content in the row intact, you can use the SORT method covered in the video.
The find and replace method also allows you to use wild-card characters when finding cells. For example, if you want to find all cells where the region is Mid-West or South-West, you can use *-west. The asterisk (*) will represent any number of characters and this criterion will select all the cells where the text ends with -west.
Code to Delete all rows where the region is Mid-West
Sub DeleteRowsWithSpecificText()
‘Source:https://ift.tt/2JZ0lzY
ActiveCell.AutoFilter Field:=2, Criteria1:=”Mid-West”
ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete
End Sub
Code to Delete records the region is Mid-West but not delete the entire row:
Sub DeleteRowsWithSpecificText()
‘Source:https://ift.tt/2JZ0lzY
Dim MsgboxAns As Integer
ActiveCell.AutoFilter Field:=2, Criteria1:=”Mid-West”
MsgboxAns = MsgBox(“Are you sure you want to delete these cells?”, vbYesNo + vbQuestion)
If MsgboxAns = vbYes Then
ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)
End If
End Sub
Code to delete rows based on a cell value (in an EXCEL TABLE)
Sub DeleteRowsinTables()
‘Source:https://ift.tt/2JZ0lzY
Dim Tbl As ListObject
Set Tbl = ActiveSheet.ListObjects(1)
ActiveCell.AutoFilter Field:=2, Criteria1:=”Mid-West”
Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
End Sub
#Excel #ExcelTips #ExcelTutorial
Calculating Staff Turnover In Power BI Using DAX – HR Insights
Recently I created a tutorial around how to calculate the amount of staff an organization has at any one time.
From this I decided to also then calculate the staff turnover calculation.
When you can get both of these results onto one chart you can actually create some compelling insights in Power BI.
You can create to high-quality HR (Human Resources) insights with Power BI.
You’ll learn exactly how to do this within this particular tutorial.
Good luck
Sam
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – https://ift.tt/2Lmxubn
FREE COURSE – Ultimate Beginners Guide To DAX – https://ift.tt/2IJLvOv
FREE – Power BI Resources – https://ift.tt/2LiAVQj
FREE – 60 Page DAX Reference Guide Download – https://ift.tt/2IJLx95
Learn more about Enterprise DNA – https://ift.tt/2LiAWDR
Enterprise DNA Membership – https://ift.tt/2IJLxWD
Excel Conditional Format Row, Column & Intersecting Cell – Excel Magic Trick 1572
Download Excel File: https://ift.tt/32Gp5Wg
In this video learn how to add Excel Conditional Formatting to a Row, a Column and the Interesting Value using three logical formulas, Mixed Cell References and the AND Logical Function.
Reference Videos:
Excel Two Way Lookup with VLOOKUP & MATCH Functions – Excel Magic Trick 1567
Excel Two Way Lookup with INDEX & MATCH Functions – Excel Magic Trick 1568
Excel Intersection Operator, An Excel Party Trick? – Excel Magic Trick 1570
Excel’s Most Silly Formula Ever Using INDEX or INDIRECT? Excel Magic Trick 1571
Add index to subgroups in Power Query
In today’s video, I am going to show you how to add individual indexes to groups in power query using the M language. #powerquery #powerbi #curbal
Here you can download all the pbix files: https://ift.tt/2yGx9Ih
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:
https://ift.tt/2NEZM2b
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: https://ift.tt/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:
https://ift.tt/2NrgChB
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://ift.tt/2I8nl09
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB
Conditional Formatting For Chart Visuals In Power BI – What’s Possible?
In this tutorial I wanted to showcase to you some examples of how you can create custom conditional formatting within charts and visuals.
This is a seriously good addition to Power BI functionality, and I’m hoping to give you some inspiration here.
There are almost unlimited ways you can create insights within your visuals no using this feature.
A great video to work through. Enjoy.
Sam
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – https://ift.tt/2Lmxubn
FREE COURSE – Ultimate Beginners Guide To DAX – https://ift.tt/2IJLvOv
FREE – Power BI Resources – https://ift.tt/2LiAVQj
FREE – 60 Page DAX Reference Guide Download – https://ift.tt/2IJLx95
Learn more about Enterprise DNA – https://ift.tt/2LiAWDR
Enterprise DNA Membership – https://ift.tt/2IJLxWD
Excel’s # 1 Silliest Formula: INDEX or INDIRECT? Excel Magic Trick 1571 (Two Way Lookup Trick)
Download Excel Start File: https://ift.tt/30JEza7
Download Excel Finished File; https://ift.tt/2xW8n7g
In this video have fun with silly and inefficient formulas to do a Two-Way Lookup using: 1) the INDEXT and MATCH Functions and the Space Operator and 2) the Defined Name Feature, The INDIRECT Functions and the Space Operator. Both formulas are for fun because there are more efficient formulas as seen in these videos:
Excel Two Way Lookup with VLOOKUP & MATCH Functions – Excel Magic Trick 1567
Excel Two Way Lookup with INDEX & MATCH Functions – Excel Magic Trick 1568
https://www.youtube.com/watch?v=TaLc6ZToY3Q
Excel Union Operator For Aggregate, Statistical & Finance Functions – Excel Magic Trick 1569
Download Excel File: https://ift.tt/30DK5uV
In this video learn about how to use the Union Operator in standard Aggregate functions, like SUM and AVERAGE and how to use it inside of parentheses inside Statistical and Finance Functions.
Related Videos:
Excel Union Operator For Aggregate, Statistical & Finance Functions – Excel Magic Trick 1569
Excel Intersection Operator, An Excel Party Trick – Excel Magic Trick 1570
Excel Intersection Operator, An Excel Party Trick? – Excel Magic Trick 1570
Download Excel File: https://ift.tt/30DK5uV
In this video learn how to use the Intersection Operator, which is a space. Learn about the #NULL Error.
Related Videos:
Excel Union Operator For Aggregate, Statistical & Finance Functions – Excel Magic Trick 1569
Excel Intersection Operator, An Excel Party Trick – Excel Magic Trick 1570