Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a direct query from Power BI Desktop to SQL Server. The table will be updated every day increasing the row number.
Below an example of raw data:
Now I need to measure the AVERAGE skipping the first 5% of row count and last 5% of row count (20 rows in this example * 5 divided by 100).
Rows = CALCULATE( DISTINCTCOUNT( 'tableBus'[ID] ) * 5 / 100 )So, this DAX give ne the number of rows that I need to skip in the beginning and in the end of table, in this example 1 line due the total count rows equal 20. Besides skip the first 5% and last 5% of lines, I need to sort the column values to calculate the AVERAGE:
In this example the average (in seconds) is 853, between value 3 and value 2447. Do you have any idea how I can get this in DAX using a Calculated Measure?
Solved! Go to Solution.
Why do you need to sort the columns to calculate the average ? won't it be the same no matter what the order ?
Help when you know. Ask when you don't!
This is a business rule, if I skip only the first 5% and last 5% I can calculate an outlier in the AVERAGE. Because of this, I need to follow this requirement to remove first 5% of min values and last 5% of max values.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |