cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
schwinnen Member
Member

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

Accepted Solutions
ChrisHaas Established Member
Established Member

Re: Top 10 with filters issue

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
ChrisHaas Established Member
Established Member

Re: Top 10 with filters issue

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

schwinnen Member
Member

Re: Top 10 with filters issue

@ChrisHaas, 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"?

ChrisHaas Established Member
Established Member

Re: Top 10 with filters issue

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.

schwinnen Member
Member

Re: Top 10 with filters issue

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?

ChrisHaas Established Member
Established Member

Re: Top 10 with filters issue

It should..

schwinnen Member
Member

Re: Top 10 with filters issue

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 450 members 3,964 guests
Please welcome our newest community members: