Languages Advisor | DAX

DAX Measures for YOY Calculation and TOPN Analysis

Learn how to develop DAX measures in Power BI for Year Over Year (YOY) calculations and showcasing the top 5 customers with the largest YOY changes in sales. Includes examples and guidance on implementation.


Empty image or helper icon

This Query related with thread "Advanced DAX Measure and Analysis Techniques"

Prompt

I am trying to create a TOPN measure that shows the TOPN customers by the Year over Year change in Average Sales Price. When I first started this measure, I noticed that the largest changes were due to customers with no sales in the prior year. You can see this in column 6 of the attached matrix.

So, I started my measure by creating the list of customers with sales in the prior year and then I built my YOY change calculation for those customers only. This is all working. However, when I try to show only the TOPN of those customers, the filter is not working in the matrix. If you look at the DAX query view, you will see that the query does return the TOP 5 only, but in the Matrix view it is not filtering only the top 5. I have two different measures in the far right of the matrix and neither works.

So your help in filtering the matrix down to the top 5 only will be appreciated. Also, I am not able to use the filter pane as this will be a dynamic page and this measure is only one of the measures I am creating. this one just ended up being more complicated.

Answer

To tackle your issue, two measures are needed:

  1. Year Over Year calculation for customers with sales in the prior year.
  2. A TOPN function to show only top 5 of these customers in terms of sales.

Both should completely solve your problem since they will take care of the YOY and TOPN constraint respectively.

Measure 1: YOY Calculation

Here, the initial calculation should be slightly modified to calculate YOY change only for customers who had sales in both the years. This will help eliminate those customers who registered sales only in one year, hence skewing the YOY calculations.

Here's one example of how you might calculate the YOY change:

YoY_AverageSalesPrice =
CALCULATE (
    AVERAGE ( Sales[Price] ),
    FILTER (
        ALL ( Sales ),
        Sales[Year] = MAX ( Sales[Year] )
            && Sales[Customer] IN CALCULATETABLE (
                VALUES ( Sales[Customer] ),
                ALL ( Sales ),
                Sales[Year] = MAX ( Sales[Year] ) - 1
            )
    )
)

This measure first determines the list of customers who had sales in the prior year and current year. It then computes the average of the Sales[Price] for those customers.

Measure 2: TOPN function

Let's now proceed to the part which deals with displaying the top 5 customers with largest YOY changes:

Top5_Customers_YoY_ASP  = 
IF (
    HASONEVALUE ( Customers[Customer] ),
    [YoY_AverageSalesPrice],
    CALCULATE (
        [YoY_AverageSalesPrice],
        TOPN (
            5,
            ALL ( Customers[Customer] ),
            [YoY_AverageSalesPrice],
            DESC
        )
    ) 
)

This measure checks if the context is a single customer (which can occur when it's being used in a visual where the calculation is broken down by customer). If so, it merely computes the YOY change. But if the context isn't a single customer, then it computes the YOY change but only for the five customers with the highest such changes. It does this through the TOPN function, where we select the top 5 Customers[Customer] based on the [YoY_AverageSalesPrice].

Remember to set visual-level filters in your matrix to the [Top5_Customers_YoY_ASP] measure for the customers you want to highlight.

I hope this helps in resolving your issue. Let me know if you encounter any problems with the solution. Don't hesitate to elaborate on your requirements further if there are additional constraints or requirements.

Create your Thread using our flexible tools, share it with friends and colleagues.

Your current query will become the main foundation for the thread, which you can expand with other tools presented on our platform. We will help you choose tools so that your thread is structured and logically built.

Description

Learn how to develop DAX measures in Power BI for Year Over Year (YOY) calculations and showcasing the top 5 customers with the largest YOY changes in sales. Includes examples and guidance on implementation.