Forum Discussion
Grouped norm dist
Hi all, I'm trying to make a grouped norm.dist calculation but can't yet figure it out. Below an example of my dataset (item, price): chair 30 couch 400 table 200 table 150 ...
Aha, Anonymous try to add three columns like:
mean2 = VAR _table = FILTER( TableName, TableName[item] = EARLIER(TableName[item]) ) RETURN AVERAGEX( _table, TableName[Price] ) med2 = VAR _table = FILTER( TableName, TableName[item] = EARLIER(TableName[item]) ) RETURN CONVERT( MEDIANX( _table, TableName[Price] ), INTEGER ) std.dev2 = VAR _table = FILTER( TableName, TableName[item] = EARLIER(TableName[item]) ) RETURN STDEVX.P( _table, TableName[Price] )it worked like:
The problem is that it calculates it over all the data ... I want to group the values based on the items. Example:
| item | price | med | mean | std.dev |
| chair | 30 | 45 | 50.25 | 33.62 |
| couch | 400 | 290 | 290 | 110 |
| table | 200 | 175 | 175 | 25 |
| table | 150 | 175 | 175 | 25 |
| chair | 11 | 45 | 50.25 | 33.62 |
| desk | 100 | null | null | null |
| chair | 60 | 45 | 50.25 | 33.62 |
| couch | 180 | 290 | 290 | 110 |
| chair | 100 | 45 | 50.25 | 33.62 |
The endgoal is to filter out outliers automatically within the report.
FreemanZ
Super User
Super UserAha, Anonymous try to add three columns like:
mean2 =
VAR _table =
FILTER(
TableName,
TableName[item] = EARLIER(TableName[item])
)
RETURN
AVERAGEX(
_table,
TableName[Price]
)
med2 =
VAR _table =
FILTER(
TableName,
TableName[item] = EARLIER(TableName[item])
)
RETURN
CONVERT(
MEDIANX(
_table,
TableName[Price]
),
INTEGER
)
std.dev2 =
VAR _table =
FILTER(
TableName,
TableName[item] = EARLIER(TableName[item])
)
RETURN
STDEVX.P(
_table,
TableName[Price]
)
it worked like: