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
schwinnen
Helper V
Helper V

Top 10 with filters issue

I am creating a bar chart for top 10 for, in my case, "Average Time at Destination".  This is a transportation scenario and we would like to know how how long a driver is spending at the delivery location.  The data is laid out as individual records, so each line item has a shipment.  So, for one destination you may have something like:

Destination Name          Shipment #     Time at Destination

ABC Co.                           Shipment 1                  61

ABC Co.                           Shipment 2                  48

ABC Co.                           Shipment 3                  71

 

So that the average for this Destination would be 60.  Getting the top 10 is easy, but getting it to work with filters has been a challenge.  There is some bad data that I don't want to include and I also only want to include destinations with more that a certain number of records.  

 

To accomplish this, I created a couple of measures.  First:

 

M1X =
CALCULATE (
    AVERAGE ( QA[TIME AT DESTINATION] ),
    FILTER ( ALLSELECTED ( QA ), QA[DESTINATION] = MAX ( QA[DESTINATION] ) ))

 

This works great.  I created a page level filter to remove some of the bad data and I still get the results I want.  At least I think I do.  I get 10 records and it seems to make sense.

 

Now, to attempt to filter out destinations with more than a certain number of records, I created this measure: 

 

M2X =
CALCULATE (
    SUM( QA[LOADS] ),
    FILTER ( ALLSELECTED ( QA ), QA[DESTINATION] = MAX ( QA[DESTINATION] ) ))

 

I am not able to create a page level filter for this measure.  When I create a visual level filter of M2X is greater that 3, instead of showing me the top 10 destinations where M2X is greater than 3, the program takes the existing top 10 and removes any where M2X is 3 or lower and leaves me with 7 or 8 records instead of 10.  

 

How do I get the program to display the top 10 (or whatever number I choose) while only showing destinations with over X number of records.  I also want to be able to adjust X to whatever I want while still showing 10 records.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Try something like this?

 

Measure =
VAR MinTrips = 5 // Set the minimum number of trips to be considered here
VAR MaxRank = 10 // Set how many different destinations to include in the ranking here
VAR FilteredQA =
    FILTER (
        ADDCOLUMNS (
            VALUES ( QA[Destination Name] ),
            "Number of Trips", CALCULATE (
                COUNTROWS ( QA )
            ),
            "Ranking", CALCULATE (
                RANKX (
                    ALL ( QA[Destination Name] ),
                    AVERAGE ( QA[Time at Destination] ),
                    ,
                    DESC, //change to ASC if you want the lowest average time to be #1 rank
                    SKIP
                )
            )
        ),
        [Number of Trips] >= MinTrips
            && [Ranking] <= MaxRank
    )
RETURN
    CALCULATE (
        AVERAGE ( QA[Time at Destination] ),
        FilteredQA
    )

Essentially, we're starting with a list of all of the Destinations in the current filter context (listening to all of your slicers).

 

Then we build a table in memory 3 columns

  1. The Destination
  2. How many deliveries it had
  3. Its rank based on average delivery time

We then filter that in-memory table down to show only the destinations that have a certain number of deliveries, and are at least a certain rank.  You set those values in the first 2 lines of code.  That will limit the list to say 10 destinations

 

Then you perform the final average by passing in that table as a filter.  This means you're only performing the average on the 10 destinations that were pre-determined.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Try something like this?

 

Measure =
VAR MinTrips = 5 // Set the minimum number of trips to be considered here
VAR MaxRank = 10 // Set how many different destinations to include in the ranking here
VAR FilteredQA =
    FILTER (
        ADDCOLUMNS (
            VALUES ( QA[Destination Name] ),
            "Number of Trips", CALCULATE (
                COUNTROWS ( QA )
            ),
            "Ranking", CALCULATE (
                RANKX (
                    ALL ( QA[Destination Name] ),
                    AVERAGE ( QA[Time at Destination] ),
                    ,
                    DESC, //change to ASC if you want the lowest average time to be #1 rank
                    SKIP
                )
            )
        ),
        [Number of Trips] >= MinTrips
            && [Ranking] <= MaxRank
    )
RETURN
    CALCULATE (
        AVERAGE ( QA[Time at Destination] ),
        FilteredQA
    )

Essentially, we're starting with a list of all of the Destinations in the current filter context (listening to all of your slicers).

 

Then we build a table in memory 3 columns

  1. The Destination
  2. How many deliveries it had
  3. Its rank based on average delivery time

We then filter that in-memory table down to show only the destinations that have a certain number of deliveries, and are at least a certain rank.  You set those values in the first 2 lines of code.  That will limit the list to say 10 destinations

 

Then you perform the final average by passing in that table as a filter.  This means you're only performing the average on the 10 destinations that were pre-determined.

@Anonymous, thanks for the response.  I appreciate you taking the time to write out that long formula.  I think I understand what that formula is doing. After that, however, I am a little confused.  What do you mean by building an "in-memory table"?

Anonymous
Not applicable

We created a variable called FilteredQA.

 

Normally, variables just hold scalar values...a single number.  But you can also set a variable to contain a table.

 

Variables only last as long as the measure is being evaluated, after that it's gone.  That's what I meant by in-memory.  The table was temporarily created, remembered, used, and then removed from memory.

 

That's all we did.  We created a table as a variable that set the criteria we needed, and then referenced that variable in our final calculation.

Excellent!!! I believe this is working.  I probably should do a little more verifying, but it looks good at first test.  

Last question - I am working off a PostgresQL database.  When I refresh the data, is this going to update to reflect new data?

Anonymous
Not applicable

It should..

Thanks a lot.  Really appreciate the help.  I will probably use this for several of my reports. 

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.