Uncover the Power of Visual Calculation in Doubling Performance for Pareto Analysis Compared to DAX Measure in Power BI

In this writing, I want to share how I started to describe Pareto analysis using the new Visual Calculation feature launched in February 2024. Plus, I'll explain how much this method boosts performance compared to using just DAX measures.

In my example, I have a Product dimension table with 5,000 items. Let's discover how to make sense of this data and start to create insightful Pareto analysis in Power BI by using Visual Calculation feature. Here, I won't utilize line and column charts due to limitations when using visual calculation features. Instead, I'll describe the analysis within a table visualization.

Below is a description of how the semantic model looks:

In the semantic model, there is a Product dimension table that contains 5,000 products.

As a first step, I generated a table visualization like below by initiating the measure [Sales:]:

Sales: = 
SUM( Sales[Sales] )

The table visualization showcases product names and their corresponding sales amounts, offering insights into the sales performance of each product, sorted by product name.

I adjusted the sorting order based on sales amount, as shown below.

Upon activating the visual calculation feature, I encountered a challenge:

the sort order couldn't be modified, resulting in a different sequence compared to the visualization shown in the upper part. To proceed with creating the Pareto analysis, the crucial first step is to display the running sum by sales amount in descending order, rather than by product name in ascending or descending order. Nevertheless, let's persist and endeavor to create the running sum by sales amount in descending order.

In attempting to utilize the RUNNINGSUM function as outlined below, I encountered a challenge again: there seems to be no method available to adjust the sort order based on sales amount.

Therefore, I opted to employ the WINDOW DAX function here and crafted the following code. It appears to be functioning properly. While the section below may not clearly indicate that it yields accurate information, the upper part distinctly demonstrates that it generates correct numbers.

Having completed the initial step, I proceeded to further develop additional visual calculations as follows, in pursuit of achieving the final result for the Pareto analysis.

Sales all = COLLAPSE([Sales:],ROWS)

Running Ratio = DIVIDE([Running Sum by Sales Desc], [Sales all])

Moving on from the visual calculation feature, by exiting from it, the visualization appears as shown below. Although the most suitable visualization type for describing Pareto analysis is the line and column chart, it's currently not supported for displaying visual calculations. Therefore, for now, I've opted to present it using the table visualization. Hopefully, this limitation will be resolved soon.

Following that, I replicated the identical visualization and achieved the same results using only DAX measures, as shown below.

Running sales by Sales Desc: =
CALCULATE (
    [Sales:],
    WINDOW (
        1,
        ABS,
        0,
        REL,
        ALL ( 'Product'[Product] ),
        ORDERBY ( [Sales:], DESC )
    )
)

Sales all: =
CALCULATE ( [Sales:], ALL ( 'Product'[Product] ) )

Running ratio: = 
DIVIDE([Running sales by Sales Desc:], [Sales all:] )

Comparing the performance in Performance analyzer, the result shows that visual calculation shows x2 better performance than only using DAX measures.

On the journey of crafting Pareto analysis using the Visual Calculation feature unveiled in February 2024, I faced hurdles with sorting and visualization options, however, I triumphantly navigated through these challenges, uncovering the ways to achieving precise outcomes. Furthermore, by contrasting to the process with only writing traditional DAX measures, I showed heightened performance.

I hope this helps adding more excitement to the exploration of the Visual Calculation feature in Power BI.