Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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.
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
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"?
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?
It should..
Thanks a lot. Really appreciate the help. I will probably use this for several of my reports.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |