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.
Hi all,
I want to calculate the maximum value per ID without outliers, let's say minus 5%.
I already have a piece of a code, see below. But this code only returns one static value and not a value calculated by ID.
This is my situation:
I have one table, named Table1 with the following columns: ID, Speed (measure), Weight, Length.
I want to calculate the maximum Weight if Speed > 0 and Length > 0 and I want to cut the top 5% (outliers).
Table1:
ID Speed Weight Length A 30 100 180 A 32 95 190 A 29 102 180 B 35 80 0 B 40 70 187 B 41 68 170 C 40 70 160 C 33 98 190 C 30 100 0
The output should be:
Table1: ID Speed Weight Length MaxWeight A 30 100 180 100 A 32 95 190 100 A 29 102 180 100 B 35 80 0 68 B 40 70 187 68 B 41 68 170 68 C 40 70 160 70 C 33 98 190 70 C 30 100 0 70
VAR UpperQuartile = PERCENTILEX.INC ( Table1; Table1[Weight]; ,95 ) RETURN CALCULATE(IF(Table1[Speed] > 0 ;MAX(Table1[Weight]));FILTER(Table1;Table1[Length] > 0 && Table1[Weight] <= UpperQuartile))
Solved! Go to Solution.
Hi @Anonymous ,
You can create your measure or column like so:
MaxWeight = VAR UpperQuartile = CALCULATE ( PERCENTILEX.INC ( Table1, Table1[Weight], .95 ), ALLEXCEPT ( Table1, Table1[ID] ) ) RETURN IF ( [Speed Measure] > 0, CALCULATE ( MAX ( Table1[Weight] ), FILTER ( ALL ( Table1 ), Table1[Length] > 0 && Table1[Weight] <= UpperQuartile ) ) )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
You can create your measure or column like so:
MaxWeight = VAR UpperQuartile = CALCULATE ( PERCENTILEX.INC ( Table1, Table1[Weight], .95 ), ALLEXCEPT ( Table1, Table1[ID] ) ) RETURN IF ( [Speed Measure] > 0, CALCULATE ( MAX ( Table1[Weight] ), FILTER ( ALL ( Table1 ), Table1[Length] > 0 && Table1[Weight] <= UpperQuartile ) ) )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This helps, thanks!!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |