Month: March 2023

What’s a Junk Dimension and when should you use it?

Posted on by Annie D

Maybe you’ve heard of the concept of a junk dimension but what exactly is it and when should you use it? Patrick breaks down this dimensional modeling concept.

Junk Dimensions
https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/junk-dimension/

Design Tip #113 Creating, Using, and Maintaining Junk Dimensions
https://www.kimballgroup.com/2009/06/design-tip-113-creating-using-and-maintaining-junk-dimensions/

Sample Script:
https://github.com/guyinacube/demo-files/blob/master/video%20demos/Synapse/20230321%20-%20Junk%20Dimension.sql

📢 Become a member: https://guyinacu.be/membership

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

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

— http://twitter.com/guyinacube
— http://twitter.com/awsaxton
— http://twitter.com/patrickdba
— <a href="http://www.facebook.com/guyinacube" Continue reading “What’s a Junk Dimension and when should you use it?” →

How To Know What Are Causing The Changes In Your Data Using DAX [2023 Update]

Posted on by Annie D

DAX is a powerful formula language used in Power BI that allows you to create complex calculations and custom measures. It enables you to analyze data at a more granular level and gain deeper insights into your business operations.

Additionally, the ability to identify and understand the root cause of changes in your data can help you make more informed decisions and take proactive steps to address any issues. In this tutorial, Sam demonstrates how you can combine a number of DAX formulas, and how to calculate and find out why and what are causing certain changes in your data.

*****Video Continue reading “How To Know What Are Causing The Changes In Your Data Using DAX [2023 Update]” →

Excel Sum Across Worksheets When Rows Are Lined Up Or Not – 2572

Posted on by Annie D

Some call it a 3D reference or a spearing formula. If your Excel worksheets are all perfectly lined up, you can use this formula to sum across worksheets in Excel.
But if your worksheets are not in the same sequence, then you can use TOCOL, SORT, UNIQUE, and SUMIFS to solve the problem.

This video answers these search terms:
how ot easy sum tabs in excel
how sum across multiple sheets in excel
how to calculate sum from other tabs in excel
how to do sum across two worksheets in excel
how to do sum of cells in multiple sheet in excel
how to get multi excel sheets Continue reading “Excel Sum Across Worksheets When Rows Are Lined Up Or Not – 2572” →

Format Total Row in Dynamic Spilled Array (Conditional Formatting) # Short Excel Magic Trick 02

Posted on by Annie D

Learn how to add conditional formatting to Dynamic Spilled Array Reports using Conditional Formatting.
Download Excel File: https://excelisfun.net/files/SEMT0002-0003.xlsx
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #microsoftexcel #microsoftmvp,
#conditionalformatting #formatting #ReportFormat
#DynamicSpilledArrays #array #arrayformula #excelformulas #formatArrays

INDEX & RANDARRAY Functions to Create Table of Data. # Short Excel Magic Trick 03

Posted on by Annie D

Learn how to create a random data set with Excel formulas that you can use for demonstrations, teaching and testing models.
Download Excel File: https://excelisfun.net/files/SEMT0002-0003.xlsx
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp,
#indexfunction #index #randarray #table #dataanalysis #dataanalytics #datatables #datatable #randomdata #radomixedata
#randarray

How to Do Running Total Sum in Excel #shorts

Posted on by Annie D

Two ways to calculate a running total in Excel.
The first method is the sum of the cell in this row and the running total from above.
The second method is an expanding dynamic range.

This video answers these common search terms:
Calculate Cummulative Sum In Excel
Excel How To Do A Cumulative Sum
How To Add Cumulative Sum In Excel
How To Calculate A Cumulative Sum In Excel
How To Calculate A Running Sum In Excel Table
How To Caluclate A Cumulative Sum In Excel
How To Create A Cumulative Sum In Excel
How To Create A Running Sum On Excel
How To Create Excel Running Sum
How To Cumulative Sum Continue reading “How to Do Running Total Sum in Excel #shorts” →

How To Identify And Optimize Slow Power BI Visuals

Posted on by Annie D

It’s crucial to pinpoint slow visuals in Power BI as slow performance can lead to a poor user experience, frustration, and potentially missed opportunities. Slow visuals can result from various factors, such as the data source, the complexity of the query, or the visualization type.

By identifying and addressing slow visuals in Power BI, users can optimize the performance of their reports and dashboards, improving the user experience and ensuring accurate and timely decision-making. In this video, Brian demonstrates how you can identify slow Power BI visuals and optimize it. It sounds pretty simple but there’s more to it that Continue reading “How To Identify And Optimize Slow Power BI Visuals” →

Using Excel To Calculate Five Number Summary – 2571

Posted on by Annie D

The five-number summary in Excel is Min, first quartile, median, upper quartile and Max. This video shows three ways:
Method 1: Three formulas
Method 2: LET function
Method 3: LAMBDA function

This video answers these Search terms:
does excel already have a 5 number summaryy option
how do you obtain a five number summary in excel
how do you use excel to find 5 number summary
how to create a 5 number summary in excel
how to create a five number summary in excel
how to determine five number summary excel
how to do 5 number summary in excel
how to do a 5 number summary in excel
how to do a five Continue reading “Using Excel To Calculate Five Number Summary – 2571” →

Count Files in Folder Using Excel? Power Query!!! Excel Magic Trick # Short 01

Posted on by Annie D

Learn how to use Power Query in Excel to cynamically count how many files are in a folder and list file names in the worksheet.
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp,

Power Query Group By: Basic to Advanced. 10 Examples. 365 MECS Class 16

Posted on by Annie D

Download Zipped Folder with All Files: https://excelisfun.net/files/16-M365ExcelClassVideoFiles.zip
This video teaches how to use the Group By feature in Power Query. The 10 examples cover many helpful applications of how to use Group By for calculations and Data Analysis.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
This video covers.
1. (00:00) Introduction and video topics
2. (00:32) What does Group By do?
3. (01:07) When to use Group By or other tools such as PivotTables
4. (01:44) Basics of Group By feature
5. (03:41) Table.Group Continue reading “Power Query Group By: Basic to Advanced. 10 Examples. 365 MECS Class 16” →

Excel Random No Repeats – Even Shorter for Microsoft 365 – Episode 2570

Posted on by Annie D

How to generate a random selection in Excel with no repeats.

Choose random names from the Data Set, but do it without repeating a namek. In a revisit to Episode #1471, how will Bill assure that there are no repeats in the Random selection? Follow along with Episode #2570 today to learn the answer and solve the challenge.

This video requires new Excel functions available in Microsoft 365. If you don’t yet have RANDARRAY, SORTBY, or SEQUENCE, then use the old method in video 1471: https://www.youtube.com/watch?v=g8FFgxmE70g

The PowerBall lotto formula shown near the end of the video is:
=TEXTJOIN(“, “,,SORT(TAKE(
SORTBY(SEQUENCE(69),RANDARRAY(69)),5)),”PB=”&RANDBETWEEN(1,26))

Table Continue reading “Excel Random No Repeats – Even Shorter for Microsoft 365 – Episode 2570” →