Category: Business

Excel Pivot Table Total of Minimums – 2588

Posted on by Annie D

Excel Pivot Table – Total of Minimums – 2588

Microsoft Excel Tutorial on Pivot Tables.
Illia K asks: “Hello, is it possible to sum min values column in grand total? If I choose sum in grand total it turn column in sum of values and I just need sum of min values. can you please help?”

In this video, I show how the Grand Total of a column of Minimums is normally the Minimum value in the column.

However, using a trick from Rob Collie and a Data Model Pivot Table with two DAX Measures, I manage to Sum the Minimums in the Grand Continue reading “Excel Pivot Table Total of Minimums – 2588” →

Use Azure SQL Managed Instance Link to have a Read Replica in the cloud!

Posted on by Annie D

Want to leverage the power of the cloud with your on-prem SQL Server? Azure SQL Managed Instance may be the answer for you! Dani joins us to talk about why MI Link can help bridge the two.

Azure SQL Managed Instance
https://learn.microsoft.com/azure/azure-sql/managed-instance/sql-managed-instance-paas-overview?view=azuresql

Azure SQL Managed Instance Link Overview
https://aka.ms/mi-link

Managed Instance (MI) Feedback to Product Group
https://aka.ms/mi-link-feedback

More Managed Instance Videos:
https://aka.ms/sqlmi-videos

Connect with Dani Ljepava:
https://twitter.com/danimir
https://www.linkedin.com/in/danimir

📢 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 Continue reading “Use Azure SQL Managed Instance Link to have a Read Replica in the cloud!” →

How To Calculate Averages Per Day In Power BI [2023 Update]

Posted on by Annie D

Power BI users often encounter the requirement of calculating averages per day when dealing with time-based data such as sales, web traffic, or social media activity. This calculation helps to provide a more accurate representation of performance trends and fluctuations over time, especially when compared to absolute values. In this tutorial, Sam will show how you can easily calculate averages per day using DAX in Power BI.

*****Video Details*****
00:00 Introduction
0:29 Data model
00:45 Total sales by customer
01:45 Averages sales per day by customer
03:10 Changing the time frame
03:48 Recap of the process
04:55 Applying the same formula against different dimensions

***** Learning Power BI? ***** Continue reading “How To Calculate Averages Per Day In Power BI [2023 Update]” →

Turn Off Excel Table Formulas and Use Cell References Instead. #Short Excel Magic Trick 10

Posted on by Annie D

Learn how to Turn Off Excel Table Formulas and Use Cell References Instead by changing the settings in Options.
Download Excel File: https://excelisfun.net/files/SEMT0008-12.xlsx
#excel #excelisfun #analytics #microsoftexcel #microsoftmvp, #short #shorts #excelmvp #excelformulas #excelfunctions #excelformulaforjobinterview #excelforbegginers #excelshorts #exceltutorial #exceltips #exceltipstricks #freeclass #freecourse #freeclasses #freeexcelcourse #freeexcel #shortsyoutube #shorts #short
#exceltable #cellreference #TableFormulas #excelonlinetraining #error

How To Fix Spaghetti Line Charts In Power BI

Posted on by Annie D

Spaghetti line charts are a type of visualization used in Power BI to show trends and patterns in time-series data. Each data series is represented by a separate line on the chart, with lines crossing each other as they move along the x-axis. This type of chart is best used with time-series data, such as financial data, website traffic data, or social media engagement metrics, as it can help identify trends and patterns in the data over time.

To ensure the accuracy and effectiveness of the chart, it is important to format the data correctly and choose the appropriate chart type Continue reading “How To Fix Spaghetti Line Charts In Power BI” →

Excel Data Bar – Smallest Number Longest Bar – 2586

Posted on by Annie D

Carlos asks how to have the smallest number have the largest data bar in Microsoft Excel.

Microsoft Excel Conditional Formatting offers three data visualization tools. Two of them are easily reversible. But Data Bars don’t have an easy way to reverse.

Nancy Faust takes us into the “Danger Zone” as I try using a devious number format to mask negative numbers as positive numbers.

Table of Contents
(0:00) Small Number Long Data Bar
(0:14) Reversing Color Bar in Excel
(0:24) Reversing Icon Sets in Excel
(0:42) Data Bar outside cell
(1:08) Negative Values Data Bar setting
(1:24) Show Bar Only in Data Bars
(1:34) Danger Zone: Devious Number Format
(2:14) Showing Continue reading “Excel Data Bar – Smallest Number Longest Bar – 2586” →

#1 Keyboard Shortcut in Excel. #Short Excel Magic Trick 09

Posted on by Annie D

Learn about the number #1 most useful keyboard in Excel: Alt + Equal Sign to invoke the SUM function.
Download Excel File: https://excelisfun.net/files/SEMT0008-12.xlsx
#excel #excelisfun #analytics #microsoftexcel #microsoftmvp, #short #shorts #excelmvp #excelformulas #excelfunctions #excelformulaforjobinterview #excelforbegginers #excelshorts #exceltutorial #exceltips #exceltipstricks #freeclass #freecourse #freeclasses #freeexcelcourse #freeexcel
#keyboard #keywords #shortsyoutube #shorts #excelkeyboards #keyboardshortcutkeys

How To Capture and Clean On-Screen Data To Use In Power BI

Posted on by Annie D

Have you ever watched a video, perhaps a tutorial, and you just want to badly try out and practice with the featured data set? In this video, Brian is going to show how you can easily capture and clean on-screen data, apply two powerful functions for reshaping stacked data, and demonstrate some cool aspects of how pivoting works all in under 9 minutes!

*****Video Details*****
00:00 Introduction
00:45 Blackbox extension
01:10 Pasting the data
02:04 Integer-Divide
03:34 Pivoting data
04:47 Cleaning nulls
06:22 Modulo

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – www.enterprisedna.co/courses/ultimate-beginners-guide-to-power-bi
FREE COURSE – Ultimate Beginners Guide To DAX – www.enterprisedna.co/courses/ultimate-beginners-guide-to-dax
FREE Continue reading “How To Capture and Clean On-Screen Data To Use In Power BI” →

Auto Numbers in an Excel Table with a Formula. #Short Excel Magic Trick 08

Posted on by Annie D

Learn how to create an auto number for records in an Excel Table using a formula.
Download Excel File: https://excelisfun.net/files/SEMT0008-12.xlsx
#excel #excelisfun #analytics #microsoftexcel #microsoftmvp, #short #shorts #excelmvp #excelformulas #excelfunctions #excelformulaforjobinterview #excelforbegginers #excelshorts #exceltutorial #exceltips #exceltipstricks #freeclass #freecourse #freeclasses #freeexcelcourse #freeexcel
#exceltable #sequencefunction #sequence #increment #indexfunction #structured #nomenclature

Excel Challenge: Most Efficient Way To REPTARRAY – 2585

Posted on by Annie D

Dave wishes that Excel would offer a REPTARRAY function, just like REPT but for arrays. This video shows four different ways to repeat an array N times.

Vote for Microsoft to add REPTARRAY:
https://feedbackportal.microsoft.com/feedback/idea/0f6d6e22-7cb3-ed11-a81b-002248519701

Details about FastExcel: http://mrx.cl/speedv4

Here are the formulas used in the video:
=TEXTSPLIT(REPT(TEXTJOIN(“,”,TRUE,A)&”,”,$E$1),,”,”,TRUE)
=DROP(REDUCE(“”,SEQUENCE($E$1),LAMBDA(a,b,VSTACK(a,D))),1)
=LET(a,E,rw,COUNTA(a),MAKEARRAY(rw*$E$1,1,LAMBDA(r,c,CHOOSEROWS(a,MOD(r-1,rw)+1))))
If you have FastExcel V4 from Charles Williams: =REPEAT(A2#,E1)

If you have a better way, please post below in the YouTube comments.

Excel Most Efficient Way to REPTARRAY Challenge
(0:00) How to REPTARRAY
(0:51) JOIN, REPT, then SPLIT
(1:52) REDUCE, SEQUENCE, LAMBDA, VSTACK
(2:51) MAKEARRAY, LAMBDA, CHOOSEROWS, MOD
(3:56) FAST EXCEL V4
(4:56) Vote for REPTARRAY
(5:42) Nancy Faust

Where are the Power BI Gateway logs? I thought I knew…

Posted on by Annie D

Do you know where the Power BI Gateway log files are? I didn’t! I’ll show you a tool I use to help figure something like this out. And also tell you where they are!

Process Monitor
https://learn.microsoft.com/sysinternals/downloads/procmon

Rui Romano’s Gateway Monitor Report
https://github.com/RuiRomano/pbigtwmonitor

📢 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
— http://www.facebook.com/guyinacube
— Continue reading “Where are the Power BI Gateway logs? I thought I knew…” →