CODEWITHSUNDEEP
powerbidax
daOnlyBG
daOnlyBG

Reputation: 601

DAX TOPN Filter Not Returning Enough Rows

I'm trying to apply a TOPN() visual filter to a Power BI sheet based on an Average Loan Amount measure. I want to see the top 5 employees with the highest average loan amount, ignoring employees who have disbursed 4 or fewer loans.

The problem I'm running into is that I don't get 5 rows returned, even though I've selected the top 5. I have to adjust the "TOPN" parameter (in the visuals) to include more than 5, just to get 5 rows.

This seems to be because when I have both the TOP5 average AND the loan count > 4 filters working, neither updates the other; that is, I can find the top 5 rows based on the average parameter, but once I include the "loan count > 4" condition, a few of the top 5 disappear, and they're not replaced by the runners-up to the original 5.

In the past, when I placed a top 5 filter for average and nothing came up, it was because all the top 5 entries all had a loan count of under 5. Once I relaxed the "TOPN" condition to be "TOP 52," I got 5 entries visible.

Does anyone know why this happens & how to fix it so I always get 5 rows returned?

EDITED TO ADD: For an example of the data, please click here. Please note that any employee with a loan count of 4 or less should be filtered out. I created the filter in PowerBI because the data sets are dynamic, and so are the filter results.

Upvotes: 0

Views: 1684

Answers (1)

Leonard
Leonard

Reputation: 2578

The fundamental problem is that you're applying 2 filters to the same visualization:

  1. You only want to include employee's with a loan count of 5 or more
  2. Of those, you want the 5 employees with the highest average loan amount

Power BI is applying both filters independently. So, it is taking the 5 employees with the highest average loan amount, and then removing 3 of them because their loan count is less than 5. I can imagine this is a common problem for people working with a Top N filter plus another filter.

One way to work around this (and I don't claim this is the only or even the best way), is to take into account the loan count before calculating the average.

For example, assuming you have the following two measures and the following data:

Loan Count = DISTINCTCOUNT(Employee[Loan Number])
Avg Loan Amt = AVERAGE(Employee[Loan Amount])

Initial Data

It's clear from the picture that Liz, Montgomery and Oscar are in the top 5 but have only 3 loans to their name.

Next, we can create a new measure that checks the Loan Count before calculating the average loan amount. If the loan count doesn't meet the threshold, you don't care about their average.

*Filtered Avg Loan Amt =  IF([Loan Count] < 5, BLANK(), [Avg Loan Amt])

This creates the following result. Notice that Liz, Montgomery & Oscar now all have no average calculated because they don't have enough loans.

Filtered Loan Amt

Now, you don't necessarily have to display the Filtered Avg Loan Amt measure on your table, but you can now use that measure in your Top N visual filter and that, by itself, will filter your table to the top 5 employees with a high enough loan count.

Filter screenshot

Notice that in my filters, I only have 1 filter (on Filtered Avg Loan Amt). I don't also need to filter to a loan count of 5 or greater. This results in the following top 5 employees:

Final Result

I hope this solves the problem you're having!

Unrelated sidenote: if you're using this threshold of 5 in a few places, I would recommend sourcing the number from an external source (including possibly a disconnected table) rather than hard-coding 5 in the measure itself. That way, if someone decides that 5 isn't the right threshold, you only have to update it one place, rather than hunting through all your measures looking for the number 5. There's an article here on using a disconnected table so that end-users can pick the threshold themselves (though it could definitely be overkill for your situation): https://powerpivotpro.com/2013/08/moving-averages-controlled-by-slicer/

Upvotes: 2

Related Questions