Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Filter "Top N" by a measure

Hi,

 

I'm trying to create a filtered table which gives me the "Top N" customers based on a measure.

 

--- Issue description ---


I've recreated the problem with dummydata, however I appear to be unable to share it due to community policy... The table I have however is rather simple:

- Date column ranging from 1/01/2019 to 1/12/2019;
- Customer column (customer A - customer Z) -- I've generated random numbers so these include both positives and negatives;

- Revenue column for the respective customers and months.

 

So basically, the table contains customers A - Z who all have revenue in every month of 2019 (can be both positive and negative). Pretty straightforward.

 

I've created a simple measure which takes the revenue of the current month and subtracts it from the revenue of the previous month, in order to map possible growth with customers, MoM:

Revenue difference previous month = CALCULATE(SUM(DummyData[Revenue])) - CALCULATE(SUM(DummyData[Revenue]);PREVIOUSMONTH(DummyData[Date]))

 

So now I have a simple table which gives me the monthly revenue per customer.

Next, I add my measure to the table. So far so good. It gives me an additional column which shows me if a customer increased in revenue or not.

 

Thereafter, I try to filter the visual (table) on Customer with Filter type "Top N", to show me the Top N (e.g. top 5 customers) By value [my aforementioned measure].

 

--- Expected result ---

 

The expected result should be the top 5 customers based on adding all the measure's result for the respective monthly revenue per customer. However, I've simulated the expected result in Excel, and it would appear that the result I'm getting is the same as when I'd be filtering on my "Revenue" column. It doesn't seem to take into account the measure itself.

To further elaborate: if customers A, B, C, D and E have the highest result according to my measure, and customer F, G, H, I, J have the highest Revenue (the data in my dummy column), using my method mentioned above results in customers F, G, H, I, J being filtered.

 

I've tried various solutions, ranging from creating a summarized table and/or calculated table to using 'What-if' parameters (RANKX). So far, none resulted in the result I am looking for.

 

Note: I am aware that the total of my measure in the table itself is incorrect, that, however, is of no interest to me. That's why it could as well be hidden.

I stumbled upon the following post, which appeared to be similar, but it didn't resolve my issue: 
https://community.powerbi.com/t5/Desktop/Top-N-filter-for-measure/m-p/241508#M107193

Many thanks in advance.

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

I have created a sample for your reference, please check the following steps as below.

1. Create a what if parameter.

Parameter = GENERATESERIES(1, 10, 1)

2. Based on that, create a measure and make the table visual filtered by it.

Measure = 
VAR r =
    RANKX (
        ALL ( DummyData[Customer] ),
        [Revenue difference previous month],
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( r <= Parameter[Parameter Value], 1, BLANK () )

Capture.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

4 REPLIES 4
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

I have created a sample for your reference, please check the following steps as below.

1. Create a what if parameter.

Parameter = GENERATESERIES(1, 10, 1)

2. Based on that, create a measure and make the table visual filtered by it.

Measure = 
VAR r =
    RANKX (
        ALL ( DummyData[Customer] ),
        [Revenue difference previous month],
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( r <= Parameter[Parameter Value], 1, BLANK () )

Capture.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Hi @v-frfei-msft 

 

Upon implementing your solution, the ranking itself still seemed to be based on the revenue figures instead of the summarization of the monthly differences. However, after tweaking the measure (Revenue difference previous month), I got the result I was searching for.

I changed the following:

Revenue difference previous month =
CALCULATE(SUM(DummyData[Revenue])) -
CALCULATE(SUM(DummyData[Revenue]);PREVIOUSMONTH(DummyData[Date]))
 
==>
 
Revenue difference previous month =
CALCULATE(SUM(DummyData[Revenue]);LASTDATE(DummyData[Date])) - CALCULATE(SUM(DummyData[Revenue]);FIRSTDATE(DummyData[Date]))
 
I reckon these filter contexts work better when referencing a specific date whilst also using a date slicer on those respective dates.
 
Worked like a charm in conjunction with the sample you referenced.
 
Thank you very much!
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.