Help Filtering only TOPN in Measure with Table Variable

DAX DAX Calculations
1

Hello,
I have been struggling for quite a while with this problem and I am hoping someone can help me…

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.

Happy to answer any questions. Thanks for your help!

TOPN Problem.pbix (308.1 KB)

2

I don’t know if I understand very well, but see if it helps you

_Top5 = 
CALCULATE (
    [YOY YTD Avg Sales Price],
    KEEPFILTERS (
        TOPN (
            5,
            ALLSELECTED ( customer[Customer Group] ),
            [YOY YTD Avg Sales Price], DESC
        )
    )
)

image
image837×258 18.7 KB

3

Hi,
Thanks for your reply. The problem with your solution, which I would consider to be a standard TOPN structure, is that the Top records it returns are customers with no sales in the prior year. That is the situation I am trying to avoid. So, you will notice in my measure I am first building a list of customers that do have sales YTD in the prior year and then I am using that list of customers to compute the TOPN. That part is working. However, the TOPN is not filtering. It is returning the measure for all customers with sales in the PY, not just the TOPN. Make sense? Thanks!

4

Check out a deep dive that I did with Data Mentor here.

https://mentor.enterprisedna.co/threads/dax-measures-for-yoy-calculation-and-topn-analysis-42219

There’s some solid ideas to review.

Sam