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 top 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!

 

 

Advertisements

Top and Bottom 10 Products by Sales Using RANKX in Power BI

In this video we’ll venture outside of the default Power BI TOPN functionality used to isolate the top and bottom N values in a visual. Because you’re an astute follower of my blog and YouTube channel, you want to know more than default functionality. The key to pulling off this feat lies with the RANKX function. By using RANKX to provide a ranking to each row in our data set, we can then determine the TOP and BOTTOM 10 values.

Of course watch the video for further breakdown, but they key to using RANKX effectively in Power BI is to use the ALL function as a parameter. In this way the contents of the entire table are considered for appropriate ranking.

The DESC or ASC parameters will enable the calculation of a rank in either descending or ascending sort order.

And finally the DENSE parameter tells RANKX how ties should be handled in the data. For example if you provide DENSE as a parameter, if 10 values are tied with a rank of 1 then the next value will receive a rank of 2.

Power BI Top 10 and Bottom 10 Thumbnail

Here is an example use of RANKX that will return a ranking of Sales Price by Product, that ranks the results in descending order (highest Sales receive the lowest ranks) and ties are in contiguous order. Watch the video to determine how to calculate the BOTTOM 10 ranking and to use RANKX to enable the top or bottom 10 values in a visualization.

ProductRank = RANKX(ALL('Company Sales Data'[Product]), calculate(sum('Company Sales Data'[Sales Price])),,DESC,dense)

As a refresher, check out this popular video to build the calendar table referenced in this video: Power BI Dashboard Tutorial: Year over Year Difference Analysis

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 a Customizable Heat Map in Power BI

In this video we’ll learn how to create a customizable heat map in Power BI without using the prepackaged downloadable visual. A heat map (or heatmap) is a graphical representation of data where the individual values contained in a matrix are represented as colors. A heat map helps draw your eye to the most and least popular areas within the matrix. The cells contained within the table either contain color-coded categorical data or numerical data, that is based on a color scale.

Matrix

Wrong Matrix

I have some fun in the video with a dashboard that I constructed using a publicly available data set from Microsoft, but in the lesson we’ll create the following:

Heatmap

Make sure to watch the video, download the data set and follow along with the instructions.

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.

Filter Top N Values with a Slicer in Power BI

In this video you will learn how to filter the top N values shown in your bar chart visualization using a slicer.

  1. This technique uses one measure that generates a number 1-10, that will be applied to a slicer.
  2. Another measure will basically rank all of the values associated with your data bars and only return the values that are less than or equal to the number you select in the slicer.

The comments that I apply to the DAX function should help make it easy to understand. I have to give a shoutout to GilbertQ from the PowerBI community for coming up with the  initial approach which I tweaked for the video.

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

How to Generate a Forecast in Power BI

In this video I’ll demonstrate how to use the forecasting analytics option in Power BI. Although Power BI’s forecast algorithm is a black box, it’s more than likely using exponential smoothing to generate results. At a very high level, exponential smoothing is an algorithm that looks for patterns in data and extrapolates that pattern into the future. To help exponential smoothing perform at an optimal level, it is very important to pick an accurate seasonality estimation, as this will have an outsized effect on the time series forecast.

If your data points are at the daily grain, then you’d use 365 as your seasonality value. If your data points are at a monthly grain, then you’d use 12 as your seasonality value. Generally, the more seasonality cycles (e.g., years) that you provide Power BI, the more predictive your forecast will be.

Without giving away the whole video, here is a pro and a con of using forecasting in Power BI.

Con: As I stated earlier the exact algorithm is a black box. Although based upon a Power View blog post, we can reasonably assume exponential smoothing is involved. Furthermore, the results cannot be exported into a spreadsheet and analyzed.

Pro: The ability to “hindcast” allows you to observe if the forecasted values match your actual values. This ability allows you to judge whether the forecast is performing well.

Check out the video; I predict you’ll learn something new.

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

Use the Power BI Switch Function to Group By Date Ranges

In this latest video, I’ll explain how to use a handy DAX function in Power BI in order to group dates together for reporting. We’ll examine a dashboard that contains fields corresponding to purchase item, purchase date and purchase cost. We’ll then create a calculated column and use the SWITCH function in Power BI to perform our date grouping on the purchase date.

Watch the video to learn how to group dates into the following aging buckets, which can be customized to fit your specific need.

  • 0-15 Days
  • 16-30 Days
  • 31-59 Days
  • 60+ Days

If you are familiar with SQL, then you’ll recognize that the SWITCH function is very similar to the CASE statement; which is SQL’s way of handling IF/THEN logic.

Even though we’re creating a calculated column within Power BI itself, best practice is to push calculated fields to the source when possible. The closer calculated fields are to the underlying source data, the better the performance of the dashboard.

Calculate Bar Chart Percent of Total in Power BI

The humble bar chart is the heart and soul of any visualization tool and is the most effective way to compare individual categorical values. We as humans are very adept at detecting small differences in length from a common baseline [1].

To quote the Harvard Business Review [2], “The ability to create smart data visualizations was once a nice-to-have skill. But in today’s complex business world, where the amount of data is overwhelming, being able to create and communicate through compelling data visualizations is a must-have skill for managers.”

If you’re going to start learning a new visualization tool, there is no better place to start than with bar chart basics. In this video I will share how to place a “percent of total” measure (i.e. value) on a Power BI bar chart. We’ll also briefly touch upon customizing the chart’s diverging color scheme.

Since Microsoft is basically giving away Power BI Desktop for free, it may become as ubiquitous as Excel. Don’t be left out!

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

References:

[1] Cotgreave, A., Shaffer, J., Wexler, S. (2017). The Big Book of Dashboards: Visualizing Your Data Using Real-World Business Scenarios. Hoboken, NJ: John Wiley & Sons, Inc.

[2] https://hbr.org/webinar/2018/02/the-right-stuff-chart-types-and-visualization-best-and-worst-practices

Make sure to watch the video for clarity but the generic version of the formula is as follows:

Vehicle Pct Total Sales = DIVIDE(sum([Measure]),CALCULATE(sum[Measure]),ALLSELECTED((‘Your Dataset’))))

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