Month: July 2019

DAX Fridays! #134: CONFIDENCE.T and CONFIDENCE.NORM, confidence intervals with DAX

Posted on by Annie D

In today’s video I will explain how to calculate confidence intervals in power bi and why they are useful.
Happy summer! #curbal #powerbi #dax #daxfridays

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: https://ift.tt/2yGx9Ih

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:
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!Here you can download all the pbix files: https://ift.tt/2yGx9Ih

▲▲▲▲▲▲▲▲▲▲

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://ift.tt/2I8nl09

************

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

View on YouTube

How To Use The GROUPBY Function In Power BI – DAX Function Review

Posted on by Annie D

In this tutorial we will run through how to use the GROUPBY function in Power BI.

This is a similar table function to the likes of SUMMARIZE and SUMMARIZECOLUMNS, but it has some unique nuances to it that are interesting and worth diving into more deeply.

You can use the GROUPBY function in a few ways and I will look to show these to you during this particular video.

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

View on YouTube

Unique Invoice / Vendor List with Items Listed Horizontally – Excel Dynamic Array Formula- EMT 1575

Posted on by Annie D

Download Excel File: https://ift.tt/2McTaa8
In this video learn how to take an invoice table, list the unique combinations of Invoice & Vendor and then list the items (references) horizontally. See the Old School Array Formula and the new Office 365 Dynamic Array Formulas. This formula takes a proper data set and does two things: 1) Lists the unique records, then 2) lists items horizontally from the reference column next to each extracted unique record.

View on YouTube

Custom Conditional Formatting For Power BI Tables

Posted on by Annie D

In Power BI we now have a wide variety of conditional formatting possibilites for tables. This wasn’t always the case.

During this tutorial I take you through a range of examples using conditional formatting rules in Power BI.

There is so much flexibility now to complete an almost unlimited amount of conditional formatting.

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

View on YouTube

OFFSET Function Dynamic Range inside COUNTIFS Function – Excel Magic Trick 1573

Posted on by Annie D

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 create a Dynamic Range using the COUNTIFS function.

View on YouTube

INDEX Function To Lookup Column inside COUNTIFS Function – Excel Magic Trick 1574

Posted on by Annie D

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.

View on YouTube

AI in Power BI #1: Anomaly detection API

Posted on by Annie D

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

View on YouTube

How And When To Use COMBINEVALUES In Power BI – DAX Function Review

Posted on by Annie D

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

View on YouTube

Excel for Business Analytics Course Trailer. Free YouTube Learning Playlist Class from excelisfun.

Posted on by Annie D

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

View on YouTube

Delete Rows Based on a Cell Value (or Condition) in Excel [With and Without VBA]

Posted on by Annie D

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

View on YouTube

Calculating Staff Turnover In Power BI Using DAX – HR Insights

Posted on by Annie D

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

View on YouTube

Excel Conditional Format Row, Column & Intersecting Cell – Excel Magic Trick 1572

Posted on by Annie D

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

View on YouTube