Build a Power BI Pop Out Slicer

Save more screen for your team! The pop out slicer panel is a perfect way to conserve space while building out your dashboard (i.e., reports) in Power BI desktop. It really is a slick feature that allows you to conserve limited reporting space by hiding your slicers until the user presses a button to reveal your data filtering options.

In this video you can watch me build out the slicer panel step by step using bookmarks, selection panel and buttons.

Power BI Pop Out Slicer (Short GIF)

  • Bookmarks are a configured view of a report page, including filters, slicers, and the state of visuals.
  • The selection panel allows you to show and hide current objects on the current report page.
  • Buttons enable users to hover, click, and further interact with Power BI content

The data sample used for this tutorial is here: https://docs.microsoft.com/en-us/power-bi/sample-financial-download

As always, do great things with your data.

Anthony B. Smoak, CBIP

 

If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

Check out other Power BI videos of interest definitely worth your time:

All views and opinions are solely my own and do not necessarily reflect those my employer.

 

How to Drill Through in Power BI

One of the great options available in Power BI is the capability to “drill through” to another report page. In this manner you can focus on a particular entity such as a customer, internal division, supplier or any other dimension of importance.

Different users have different data needs. When designing a dashboard typically the Key Performance Indicators (KPIs) are aggregated at a high level on the initial visualization. This offers executives and management types a “bird’s eye view” of performance.

Personally, I am a fan of BANs (look up the term in a dashboard design context if you are not familiar) when I want to highlight key takeaways.

BANs

Subsequent lower level dashboard pages can offer analysts and others the ability to either explore data with additional interactivity or simply display a static detailed report. The point is to start at a high level and allow your user to drill to a more granular level of data.

In this video I demonstrate the use of the drill through functionality in Power BI. In this scenario, you are the Chief Supply Chain officer trying to gauge your Perfect Order Percentage KPI for several internal divisions. When it’s time to sit down with your four division mangers to discuss their performance on this metric, you want the ability to start at a high level and then drill through to a static report based upon their respective internal divisions or on a specific shipping error.

DrillThrough

Drill through on “In Full Delivery” error category

Do not try and cram every visualization, chart, table or gauge under the sun into a dashboard! Take advantage of drill through functionality and tailor your data presentation for specific user groups. This general concept applies to any data visualization tool, but if you’re using Power BI then this video will help you understand the specific steps required to enable drill through functionality.

I’m frequently questioned where I obtain mock data for my scenarios. My secret source is mockaroo.com which is a great starting point for developing test data.

As always, do great things with your data.

Anthony B. Smoak, CBIP

 

If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

Check out other Power BI videos of interest definitely worth your time:

All views and opinions are solely my own and do NOT necessarily reflect those my employer.

How to Create a Dashboard in Tableau

I took the time to produce a dashboard series that would get a relatively new Tableau user up to speed in very little time. I put together the “Goldilocks” videos I wish I had when I was a novice; not too short but long enough to hammer the concepts home.

In the first video, I dive head first into constructing four basic charts that I believe every data user should know how to put together. You will watch me demonstrate how to put together the following charts:

Line Chart with Forecast

Tableau Dashboard Line Chart

The shaded area is a time series forecast predicting the number of orders for the year 2020.

Map

Tableau Dashboard Map

Heat Map

Tableau Dashboard Heat Map

Bar Chart

Tableau Dashboard Bar Chart

In the second video, I’ll cover the layout and formatting of the dashboard, as well as adding a little interactivity. When the user hovers the cursor over the Line Chart, all of the other charts will update to reflect the number of orders represented since the selected month and year.

Full Dashboard

Tableau Dashboard

Watch Part 1 to Build the Component Charts

Watch Part 2 for Layout and Interactivity

What You need:

  • Either Tableau or Tableau Desktop
  • Data set: Tableau Superstore Data (can be found all over the internet with a simple Google search).

Do some great things with your data!

If you find this type of instruction valuable make sure to subscribe to my Youtube channel. All views and opinions are mine alone, independently researched and do not necessarily represent those of my employer.

Tableau Bar Chart: Combining Small Values

In this video we’ll learn how to build a bar chart visualization that combines values below a certain threshold into an “OTHER” bucket. This technique is very useful when limiting the number of bars to show on your visualization while not losing sight of all the smaller values.

  • Using standard Tableau Superstore data, we’ll calculate the percentage of sales that are generated by each individual state.
  • We’ll then use a parameter to set a percentage threshold where all states below this percentage will be combined.
  • This technique also requires the use of sets and Fixed LODs.

This technique allows us to combine all states below a certain threshold (e.g., 2%) into one single bar chart showing a combined 24%.

Bar Chart Below Threshold Thumb 01

I have to give credit where credit is due to Ann Jackson for sharing this technique at TC19!

Feel free to interact with the viz and download the workbook on Tableau public:

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

All views and opinions are solely my own and do NOT necessarily reflect those my employer.

 

Build a Stacked Donut Chart in Tableau

Have you ever wanted to stack 4 pie charts on top of each other to build a visual? Let’s have some fun building out a stacked donut chart or a “TrailBlazer” chart as I call it due to its likeness to a particular NBA team’s logo.

Stacked Donut Chart Thumb

Portland Trailblazers anyone?

Stacked Donut vs Trailblazer

In order to build out this chart I used an innovative technique shared by Simon Runc on the Tableau forums. Feel free to check out that post here.

Using Tableau’s Sample Superstore Data, Simon came up with an innovative use of the INDEX() function and the Size functionality to create three different pie charts that each show a respective percentage of a measure (in this case Sales) to the Total amount of the measure.

For example, the chart highlights in red the percentage of Consumer Sales as a percentage of all segment sales (i.e., Consumer + Corporate + Home Office). The grey portions represent all other sales, other than the segment of interest.

The trick to this approach is using the Index function to create a pie chart per segment. For example Consumer is assigned a value of 1, Corporate a value of 2 and Home Office is assigned 3. When the INDEX() value is placed on size, the three different charts are assigned sizes where one is slightly larger than the next.

Stacked Donut Raw 2

With a little division and axis customization, the three segments are placed on top of each other to provide a stacked pie chart effect. The hole is courtesy of the standard methodology for creating a donut chart which involves a dual axis.

It makes much more sense when you see it in action so make sure to watch the video!

Here is an example of the raw stacked donut chart before the “TrailBlazer” formatting.

Stacked Donut Raw

Feel free to interact with the viz and download the workbook on Tableau public:

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

Definitely check out other posts of interest for building donut charts in Tableau:

All views and opinions are solely my own and do NOT necessarily reflect those my employer.

Select Random Sample Values and Rows using Excel

In this video I help you solve the dual problems of selecting a random value from an Excel list and selecting a number of random rows from a range of data in Excel. At times when I am generating a data-set to use in my video tutorials, I want to select a random selection of rows. Typically, because my data values are clumped together and are too similar to the data on preceding and subsequent rows.

SELECTING A RANDOM VALUE FROM A LIST

Enter three Excel formulas to save the day for selecting a random value from a list:

  • ROWS()
    • Returns the number of rows in a reference or array.
  • RANDBETWEEN()
    • Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated.
  • INDEX()
    • The INDEX function returns a value or the reference to a value from within a table or range.

Randow Row in Excel Blog Screenshot

  1. In the screenshot above notice that the ROWS() function returns the value of 20, which corresponds to the number of names listed in the cell range of A6 to A25.
  2. The RANDBETWEEN() function generated a random number between 1 and the value returned from ROWS() (i.e., 20). In this case, RANDBETWEEN() combined with ROWS() returned a value of 3.
  3. By combining the results from the first 2 functions, the INDEX() function searches our list and returns the value of the 3rd cell in the list  (i.e., Flor McCard) because the RANDBETWEEN() function returned a value of 3.

When we put it all together it looks like the following:

=INDEX($A$6:$A$25,RANDBETWEEN(1,ROWS($A$6:$A$25)))

I choose to use the absolute cell reference notation with dollar signs although in this case it is not necessary since we are not copying our results to other cells.

SELECTING RANDOM ROWS FROM A LIST

We’ll only use 1 Excel formula to save the day for selecting random rows from a range:

  • RAND()
    • RAND() returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.

By placing the RAND() function in a column co-located with your data, you will assign a random number to each row in your data-set or range.

Once that is done, all you have to do is sort your data by the RAND() column and then select however many rows you need. It’s that simple!

If you are like me, you probably need to see it in action to get a better understanding. Check out the video above and if you learned something, please go ahead and like it on my Youtube channel!

Thanks for your support!

All views and opinions are solely my own and do NOT necessarily reflect those my employer.

References:

All Excel function definitions are from https://support.office.com

 

 

 

 

Build a Tableau Parameter Action Dashboard

This video is inspired by Filippos Lymperopoulos who has a great article on parameter action concatenation. Definitely check out his article here. I am a hands on person who learns from building and sharing, therefore I put together this video to explore, tweak and hammer home the concept.

In this video we’ll build a Sales Analysis dashboard in Tableau using Parameter Actions and the Concatenation Aggregation functionality. The great thing about this tip is that you can use it across multiple data sources. This is a must see!

This approach relies upon the use of two different data connections. In this manner our data tables are completely un-joined without an established relationship. We have a data-set comprised of a customer list and one comprised of customer transactions across three years.

The key to linking the data sets together relies upon the following calculated field which creates a de-facto set that we can use to highlight customer purchases:

Tableau Concat Calc Field

When we setup a concatenation parameter action on our dashboard, the very act of selecting a [Customer Name] will add that Customer Name to the parameter named [Selected Customer]. This will cause all selected customers to resolve to TRUE, which allows highlighting of the sales bar charts related to the user selected customers.

Tableau Concat Parameter Action Thumb1

In the screenshot above, notice the selected [Customer Name] values on the left hand side are also concatenated together at the bottom of the dashboard (i.e., Franciso Hernandez, Jose Garcia, and Terrye Marchi). All of their respective purchases are also highlighted in the middle of the dashboard.

Tableau Concat Calc Field Label

The above calculated field is used to only show the [QTY] purchased for the user selected customer and is placed on the bar chart label.

Feel free to interact with the viz and download the workbook on Tableau public:

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

All views and opinions are solely my own and do NOT necessarily reflect those my employer.

How to Highlight the Bottom Bar Chart Values in Tableau

 

I decided to make this video after someone left a comment on another video I made titled “How to Highlight the Top 3 Bar Chart Values in Tableau” asking how to find the last three values.

Youtube Comment Highlight Bottom 3

In this video I will show you how to highlight the bottom three sales values on a bar chart. You’ll also learn how to use a parameter to dynamically change the number of lowest bars highlighted.

Bottom 3 Bar Chart Values Thumbnail

We can accomplish the highlighting of the bottom N bar chart values via two ways. We can either create a set or create a calculated field to accomplish this task. The set method is cleaner but has its limitations when multiple dimensions are used in the visual. Therefore, the calculated field approach serves us well when we add multiple dimensions.

Watch the video to see how it all comes together but the calculation boils down to this:

RANK(SUM(0-[Sales]))<=[Highlight Parameter]

By adjusting the [Highlight Parameter] control, the user can determine how many bottom sales values are highlighted in the visual. This method also maintains its functionality when an additional dimension is added to the visual.

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

All views and opinions are solely my own and do NOT necessarily reflect those my employer.

Create Multiple Bar Charts in Python using Matplotlib and Pandas

In this Python visualization tutorial you’ll learn how to create and save as a file multiple bar charts in Python using Matplotlib and Pandas. We’ll easily read in a .csv file to a Pandas dataframe and then let Matplotlib perform the visualization. As a bonus you’ll also learn how to save the plot as a file.

The key to making two plots work is the creation of two axes that will hold the respective bar chart subplots.

# define the figure container and the two plot axes
fig = plt.figure(figsize=(20,5))

# add subplots to the figure (build a 1x2 grid and place chart in the first or second section)
ax1 = fig.add_subplot(1,2,1)
ax2 = fig.add_subplot(1,2,2)

Understanding the subplot nomenclature is essential. Adding axes to the figure as part of a subplot arrangement is simple with the fig.add_subplot() call. In this arrangement the first digit is the number of rows, the second represents the number of columns, and the third is the index of the subplot (where we want to place our visualization).

Of course you need to watch the video to see how all of the code comes together.

Also, keep this Matplotlib style sheet reference handy for changing up the style on your visual.

NBA Blocks Assists

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

All views and opinions are solely my own and do NOT necessarily reflect those my employer.

See the following links for additional background:

https://matplotlib.org/3.1.0/gallery/subplots_axes_and_figures/subplots_demo.html

https://matplotlib.org/3.1.1/api/_as_gen/matplotlib.figure.Figure.html#matplotlib.figure.Figure.add_subplot

Sum Top and Bottom 10 Products by Sales in Power BI

In this video we will cover how to calculate the aggregate sum of only the Top and Bottom 10 Product Sales using DAX in Power BI. There are always multiple ways to accomplish a task with Power BI and DAX but I will share the technique I used to visualize the Bottom 10 Sales Products when there is a rare single tie among the products. The solution may be a bit over-engineered to my data-set but the aim is to share an approach you can use to tackle similar data issues in your dashboards. It’s well worth the watch!

I won’t give way the whole video but I’ll share the DAX formula to sum the Top 10 products by Sales Price from my table named ‘Company Sales Data’.

1_SumSalesTop10Products = 
CALCULATE(
          SUM('Company Sales Data'[Sales Price]),
          TOPN(
               10,GROUPBY('Company Sales Data','Company Sales Data'[Product]),
               CALCULATE(sum('Company Sales Data'[Sales Price]))
              )
         )

I have created a variable named 1_SumSalesTop10Products that uses the CALCULATE function to

  • SUM the [Sales Price] variable from the [Company Sales Data] table (see the first argument to the CALCULATE function);
  • But it only sums the [Sales Price] for the TOP 10 highest selling products, because we use the TOP N function to create a temporary table that only returns the products with the 10 highest aggregated Sales Prices;
    • The GROUP BY function is used to aggregate the table rows by product and then the CALCULATE argument sums the Sales Price for the aggregated products;

Don’t let this scare you off, watch the video to get a better understanding, and to learn how I sum the Bottom 10 products by Sales Price.

As always, get out there and do some great things with your data!