Tag: DAX

Scenario Analysis Patterns in Power BI using DAX

Posted on by Annie D

Gain access to resource download by registering here – http://bit.ly/2EV1hne

What’s it all about? We’ll be reviewing a number of techniques specifically around scenarios analysis in Power BI. Don’t just think Power BI is just a visualization tool, it is a powerful analytical engine that can produce amazing insights including running custom scenarios on your raw data.

***** Learning Power BI? *****

All Enterprise DNA TV Resources – http://bit.ly/2ZhmVtV…

FREE COURSE – Ultimate Beginners Guide To Power BI – http://bit.ly/2ESTLcy…

FREE COURSE – Ultimate Beginners Guide To DAX – http://bit.ly/2ESTLcy…

FREE – Power BI Resources – http://bit.ly/2ZfnaWh…

Learn more about Enterprise DNA – http://bit.ly/2RacRD3

View on YouTube

Average Last 3 Customer Sales: Power Query, DAX Measure or Worksheet Formula

Posted on by Annie D

Download Excel Finished Files: http://bit.ly/2K9SNfn
Entire page with all Excel Files for All Videos: http://bit.ly/1kSFWvs

In this video learn how to Average a customers last three sales (last three dates) using Excel Worksheet Formulas, Power Query M Code and a Power Pivot & Power BI DAX Measure.
Topics:
1. (00:07) Introduction
2. (00:50) Sort Method
3. (02:09) Excel Worksheet Formulas, including AGGREGATE and AVERAGEIFS functions.
4. (05:27) LARGE Function and New Excel Office 365 Calculation Engine
5. (08:15) Power Query M Code, including Table.Sort, List.Average and List.FirstN M Code functions
6. (12:46) Power Query Parameter Query with condition coming from Excel Worksheet
7. (14:05) Power Pivot & Power BI DAX Measure, including TOPN, CALCULATE and AVERAGE DAX Functions
8. (17:08) DAX Parameter from Excel Worksheet using VALUES DAX Function.
9. (18:43) Add new records to table and test formulas
10. (19:00) Fix Power Query Table.Sort bug using Dummy Insert Column
11. (20:08) Fix Power Query Table.Sort bug using Table.Sort function inside Table.Group Function
12. (22:12) Conclusion

View on YouTube

Enterprise DNA Learning Summit – May 2019, New Power BI Online Experience

Posted on by Annie D

EVENT DETAILS – http://bit.ly/2VlegnG

Session 6 – New Power BI Online Experience

During the final session, we’ll be running through some of the many great changes to the Power BI online service.

There has been so many, and they have all be huge updates. I look forward to showing and telling you what I think about them!

• Reviewing new features in online service
• New home page experience
• Managing apps, workspaces & reports
• Distributing your insights effectively
• Updated best practices for management

View on YouTube

Enterprise DNA Learning Summit – May 2019, Session 5

Posted on by Annie D

EVENT DETAILS – http://bit.ly/2VlegnG

Session 5 – Ranking Techniques

In the 5th session, we’ll be covering a variety of ranking techniques that can be used in many different environments and data scenarios.

Formulas such as RANKX and TOPN are some of the harder to learn and understand in Power BI, so I plan to complete a deep dive into them and really show you how they work.

• Covering nuances of RANKX function
• Diving into key topic of ‘context’
• Extending ranking insights further
• Reviewing TOPN and how it works
• Isolate top or bottom sales

View on YouTube

Enterprise DNA Learning Summit – May 2019, Logistics Management

Posted on by Annie D

EVENT DETAILS – http://bit.ly/2VlegnG

Session 4 – Logistics Management

In this session, I will be covering a topic I’ve haven’t reviewed too often before around analyzing events through time.

We’ll be running through a common problem developers have when working with Power BI. That is, dealing with multiple dates and creating compelling analysis around them.

• Events in progress pattern
• Managing multiple dates in fact tables
• Unique data model techniques
• Branching out into complex logic
• Advanced DAX function insights

View on YouTube

Enterprise DNA Learning Summit – May 2019, Session 3

Posted on by Annie D

EVENT DETAILS – http://bit.ly/2VlegnG

Session 3 – Cluster & Groups Analysis

In this session we’ll be covering a grouping technique that allows you to review clusters of your data points.

This is a very powerful analysis technique in Power BI that is incredibly flexible. You can use it in nearly any analytical scenario that you come across.

I can’t wait to show you grouping technique which will have a lot of real-world application for anyone who’s looking to complete this type of analysis with Power BI.

• Creating dynamic group insights
• Editing visual interactions smartly
• Extending supporting tables in models
• Grouping formula patterns
• Highlight important clusters of information

View on YouTube

Enterprise DNA Learning Summit – May 2019, Session 2

Posted on by Annie D

EVENT DETAILS – http://bit.ly/2VlegnG

Session 2 – Parameter Tables

In the second live session, we’ll be covering parameter tables and how you can use them in Power BI

I will be running through a real-world discounting scenario where using parameter tables is essential. The largest takeaway from this session is how to think about the variables that you can include in your data and how far you can take your analysis.

Sometimes it’s difficult to know where to start with this type of work but I plan to go through my thought process here and hopefully many of these tips can move into your own development processes over time.

• Creating and using parameter tables
• How to plan for implementation
• Real-world discounting scenarios
• Extending calculations w/measure branching
• Showcasing the key insights

View on YouTube

Enterprise DNA Learning Summit – May 2019, Session 1

Posted on by Annie D

EVENT DETAILS – http://bit.ly/2VlegnG

Session 1 – DAX Basics & CALCULATE

During the very first session of the event, I’ll be completing a quick intro and then run through the data and resources we’ll be using. The main part of the session will be about covering key beginner to intermediate concepts around DAX functions.

DAX is crucial to really mastering Power BI and creating compelling analysis, so will look to review what I believe to be essentials for those just starting out with Power BI.

• Intro to key DAX functions
• Deep dive into CALCULATE
• Time Intelligence Formulas
• Cumulative Totals
• Unique Ranking Technique

View on YouTube

Cross Selling Matrix Deep Dive – Power BI & DAX Tutorial (Market Basket)

Posted on by Annie D

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

Comprehensive Introduction to Excel Power Pivot, DAX Formulas and DAX Functions

Posted on by Annie D

Download Excel START File: https://ift.tt/2FrxeX5
Second Excel Start File: https://ift.tt/2Dtf4Sf
Download Zipped Folder with Text Files: https://ift.tt/2Frxfu7
Download Excel FINISHED File: https://ift.tt/2qSnYkx
Download pdf Notes about Power Query: https://ift.tt/2FrxwgD
Assigned Homework:
Download Excel File with Instructions for Homework: https://ift.tt/2qRom2T
Examples of Finished Homework: https://ift.tt/2Frxgyb

This video teaches everything you need to know about Power Pivot, Data Modeling and building DAX Formulas, including all the gotchas that most Introductory videos do not teach you!!!

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

Topics:
(00:15) Introduction & Overview of Topics in Two Hour Video
1. (04:36) Standard PivotTable or Data Model PivotTable?
2. (05:51) Excel Power Pivot & Power BI Desktop?
3. (12:31) Power Query to Extract, Transform and Load Data to Data Model – Get data from Text Files, Relational Database and Excel File.
4. (25:47) Build Relationships
5. (27:43) Introduction to DAX Formulas: Measures & Calculated Columns
6. (29:15) DAX Calculated Column using the DAX Functions, RELATED and ROUND
7. (31:20) Row Context: How DAX Calculated Columns are Calculated: Row Context
8. (33:49) We do not want to use Calculated Column results in PivotTable using Implicit Measures
9. (34:05) DAX Measure to add results from Calculated Column, using DAX SUM Function.
10. (35:29) Number Formatting for DAX Measures
11. (36:35) Data Model PivotTable
12. (39:31) Explicit DAX Formulas rather than Implicit DAX Formulas
13. (41:50) Show Implicit Measures
14. (45:00) Filter Context (First Look) How DAX Measures are Calculated
15. (50:14) Drag Columns from Fact Table or Dimension Table?
16. (53:30) Hiding Columns and Tables from Client Tool
17. (55:52) Use Power Query to Refine Data Model
18. (57:54) SUMX Function (Iterator Function). DAX Measure for Revenue using the SUMX Function to simulate Calculated Columns in DAX Measures
19. (01:01:00) Compare and Contrast Calculated Columns & Measures
20. (01:04:26) Why We Need a Date Table. Why we do NOT use the Automatic Grouping Feature for a Data Model PivotTable
21. (01:06:46) Build an Automatic Date Table in Excel Power Pivot. And then build Relationship.
22. (01:11:00) Introduction to Time Intelligence DAX Functions. See TOTALYTD DAX Function
23. (01:13:47) Introduction to CALCULATE Function: Function that can “see” Data Model and can change the Filter Context. (01:18:00) Also see the ALL and DIVIDE DAX Functions. Create formula for “% of Grand Total”. Also learn about (01:21:30) Context Transition and the Hidden CALCULATE on all Measures.
24. (01:27:18) DAX Formula Benefits.
25. (01:28:00) Example of DAX Formula that is easier to author than if we tried to do it with a Standard Pivot Table or Array Formulas
26. (01:31:50) AVERAGEX Function (Iterator Function) to calculate Average Daily Revenue.
27. (01:34:00) Filter Context (Second Look) AVERAGEX Iterator Function
28. (01:36:16) Build Dashboard. Create multiple DAX Formulas. Create Multiple Data Model PivotTables and a Data Model Chart.
29. (01:38:38) Create Measures for Gross Profit and Gross Profit %
30. (01:41:27) Continue making more Data Model PivotTables.
31. (01:41:50) Make Data Model Pivot Chart.
32. (01:45:10) Conditional Formatting for Data Model PivotTable.
33. (01:46:22) DAX Text Formula for title of Dashboard
34. (01:47:50) CUBE Function to Convert Data Model PivotTable to Excel Spreadsheet Formulas.
35. (01:50:05) Adding New Data and Refreshing.
36. (01:50:40) Update Excel Power Pivot Automatic Date (Calendar) Table. Clue is the blank in the Dimension Table Filter.
37. (01:52:20) How to Double Check that a DAX Formula is yielding the correct answer?
38. (01:53:22) DAX Table Functions. See CALCULATETABLE DAX Function.
39. (01:55:07) DAX Studio to visualize DAX Table Functions, and to efficiently create DAX Formulas
40. (02:00:12) Existing Connections to import data from Data Model into an Excel Sheet
(02:03:15) Summary

View on YouTube

Power BI M Code for Moving Annual Total (MAT): Custom Function Power Query Custom Column – MSPTDA 10

Posted on by Annie D

Download Power BI Desktop START File: https://ift.tt/2BXmQEV
Download Power BI Desktop FINISHED File: https://ift.tt/2MZj7e5
Download pdf Notes about Power Query: https://ift.tt/2BXmSg1
Download Excel File with parallel Excel Example: https://ift.tt/2ojpNpi
Assigned Homework:
Download pdf file with homework description: https://ift.tt/2PfRSts
Example of Finished Homework in Power BI Desktop: https://ift.tt/2ojpP0o

In this Video learn Power Query M Code and Custom Functions to calculate Moving Annual Toatls.
Topics:
1. (00:15) Introduction
2. (01:10) Comment from YouTube that inspired the video. Verbal Description of the Data Model Transformation we want to make, including the Moving Annual Total Calculation.
3. (02:07) Thanks to Bill Szysz for Custom Function.
4. (02:18) Excel Example of Moving Annual Total
5. (03:30) Why Power Query and not Excel or DAX?
6. (03:43) Look at final solution and Custom Function to see what we are trying to accomplish, including a method to filter a table with in a Custom Column in Another Table and have the formula see criteria from the the Inner Table and the Outer Table.
7. (05:37) Step 1: Look at how we imported files
8. (06:07) Step 2: Extract a Sorted Unique List from the source Facet Table. Use Production Operator to get a List, then use the Table.Distinct and Table.Sort functions.
9. (07:31) Step 3: M Code to create a Crossjoin of all combinations of Months and Product Names with the steps: Extract Column, Convert to Start of Month, Extract Min and Max Dates, use List.Dates function to create range of dates, then merge using Custom Column to get all combinations of Months and dates.
10. (14:39) Step 4: Group BY Date and Product to get Monthly Totals.
11. (16:25) Step 5: Create Final Table with the steps: Merge Step 3 and Step 4, Remove Nulls, Add Custom Column to get One Year Back.
12. (20:15) Step 5: Sort and how it is different than Excel Sport.
13. (21:25) Step 5: Table.Buffer Function allows us to Buffer the Internal Table to prevent a call to the source table for every row in the table.
14. (22:22) Step 5: create Custom Column with Function to Calculate Moving Annual Totals (MAT).
15. (28:41) Add new data to test if everything updates
16. (29:06) Summary

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

View on YouTube

Microsoft Power Tools for Data Analysis: Dashboards & Reports. Class Introduction Video. MSPTDA #01.

Posted on by Annie D

Download Excel & pdf Files: https://ift.tt/2tiUtJK
This video introduces the topics that will be covered in this Highline College BI 348 Class:
Name of Class:
BI 348 – Microsoft Power Tools for Data Analysis:
• Power Query
• Power Pivot
• DAX
• Power BI Desktop
• Excel
For Creating:
• Data Models, Reports, Dashboards and Analytics
Taught by Mike excelisfun Girvin, Excel MVP 2013-2018
• A class about connecting to multiple source of data, transforming the data into a refreshable & dynamic data model, and building reports and dashboards to provide insightful and actionable information.

Prerequisites for this class:
• Busn 216: Excel Basics, https://www.youtube.com/playlist?list=PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k
• Busn 218: Advanced Excel, https://www.youtube.com/playlist?list=PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw
• Busn 210: Business Statistics, https://www.youtube.com/playlist?list=PLrRPvpgDmw0ngx_uPhvasTbOWLOztsaBj

What Version of Excel:
• Office 365 (updated each month)

What Version of Power BI Desktop:
• Free Tool we will download (update each month)

Over View of Topics for the class:
1. Data Analysis / Business Intelligence terms and concepts that we will learn in this class:
• Proper Data Set
• Fact Table
• Dimension Tables
• Relationships
• Star Schema
• ETL
• Measures
• Dashboards
• SQL
• Data Warehousing
 
2. Learn how to use Excel Power Query:
• Import Data from multiple sources
• Clean and Transform Data
• Create Data Components for Star Schema Data Models
• Load Data To Excel, the Data Model and Connection Only
• Replace Complicated Excel Solutions with Power Query Solution
• Use the Power query User Interface to create Power Query Solutions
• Learn about the Case Sensitive, Function-based M Code Language that is behind the scenes in Power Query
3. Learn how to use Excel Power Pivot:
• Excel Power Pivot provides:
i. Data Model where we can have multiple tables, formulas and relationships (Star Schema)
ii. Columnar Database to hold “Big Data” and process quickly over that “Big Data”
iii. New Formula Language called DAX:
1. Many More Calculations than in Standard PivotTable
2. Build One Formula that can work in many reports
3. Add Number Formatting to Formulas
• Excel Power Pivot to:
i. Replace VLOOKUP Formulas and Single Flat PivotTable Data Source with Multiple Tables, Relationships in the Data Model to create more efficient Reports & Dashboards
ii. Use Power Pivot Columnar Database to hold millions of rows of data
iii. DAX formulas have more Power than Standard PivotTable Calculations
4. Learn about Building Star Schema Data Models:
a. Why they are important in Power Pivot and Power BI Desktop
b. How to build them using:
i. Power Query
ii. Power Pivot
iii. DAX
iv. Power BI Desktop
5. Learn how to author DAX Formulas for Excel’s Power Pivot & Power BI Desktop:
a. Calculated Column Formulas for Data Model
b. Measure Formulas for PivotTables
c. DAX Functions like SUMX, CALCULATE, RELATED, and Much More…
d. Lean why we must create Explicit rather than Implicit formulas
e. Learn how Row Context works in formulas
f. Learn how Filter Context works in formulas
g. Learn about Scalar & Table Functions
h. Use DAX Studio to visualize and analyze DAX Formulas
6. Learn how to use Power BI Desktop:
a. Power Query to import, clean, transform and create Star Schema Data Models
b. Create Relationships
c. Create DAX Formulas
d. Build Interactive Visualizations
e. Build Dashboards
 
7. Learn how to use Excel:
• Spreadsheet Formulas & Functions
• Standard PivotTables
• Power Query
• Power Pivot
• Build Data Model PivotTables and the resultant Reports, Dashboards and Analytics
8. Building Refreshable, Insightful Dashboards
a. Build Excel Dashboards
b. Build Power BI Dashboards
9. Case Studies to practice using Power Pivot & Power BI Desktop for Reporting, Building Dashboards and Building Business Analytics Solutions

View on YouTube