calculate the % of how many times the value of a measure is under the average of that same measure

[jpurdue]

Hello guys, been struggling with getting the below to work. I have a table with 2 columns "ID",  "MPH", "1or0". Column "MPH" is a custom measure that = moves/time. I'm trying to make a custom measure that shows the percentage of ID's under the average of the MPH measure.

ID         MPH     1or0

1            8.9         1

2            9.8         0

3            15.2       0

4            5.2         1

5            6.3         1

_____________

             9.15

I've been able to use an IF statement to check this, and it works.

1or0 = IF([MPH] < AVERAGEX(ALL('TABLE'[ID]),[MPH]),1,0)

I then use the below to do the calculation but the column is blank on everything and I haven't a clue why. I've tried searching the forum but nothing helps. Anyone see anything wrong with this?

%under =

VAR x_under = CALCULATE(COUNTROWS(FILTER('TABLE',[MPH] < AVERAGEX(ALL('TABLE'[ID]), [MPH]))))

VAR x_total = DISTINCTCOUNT("TALBE'[ID])

RETURN DIVIDE(x_under, x_total) * 100

[v-kaiyue-msft]

Hi @jpurdue ,

This is what seems to be a measure aggregation issue, more details can be found in the link: Design Pattern - Groups and Super Groups! - Microsoft Fabric Community.
You can modify the expression to:

%under =
VAR _table = SUMMARIZE('Table','Table'[ID], "under",'Table'[MPH])
VAR _under = AVERAGEX(_table,'Table'[MPH])
VAR x_under = CALCULATE(COUNTROWS('Table'),FILTER(_table,'Table'[MPH] < _under))
VAR x_total = DISTINCTCOUNT('Table'[ID])
RETURN
DIVIDE(x_under,x_total) * 100

The result obtained is shown below:

Also I noticed that some of the table names in your expression have spelling errors, make sure you reference the table names correctly.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jpurdue]

Hello Clara! Thanks for the reply! I think my issue with the above calculation is that the first part of the calculation (_under) brings back an average of an average. 

VAR _table = SUMMARIZE('Table','Table'[ID],"under",[MPH])

VAR _under = AVERAGEx(_table,[MPH])

ID      MPH        MOVES       HOURS

1         9.36           103              11

2         6.3               63              10

3         7.2               65                9

The total MPH should be 7.7, not 7.62. And VAR _under = AVERAGEx(_table,[MPH]) is bringing back 7.62

[v-kaiyue-msft · Accepted solution]

Hi @jpurdue ,

You can use the ROUNDUP function if you want the result to be rounded up, which rounds a number up away from 0 (zero). For more information see the documentation: ROUNDUP function (DAX) - DAX | Microsoft Learn.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.