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:
FreemanZ
Super User
Super Userhi Anonymous
try to create a calculated table like:
Table =
ADDCOLUMNS(
VALUES(TableName[Product]),
"mean",
CALCULATE(MEAN(TableName[Price])),
"median",
CALCULATE(MEDIAN(TableName[Price]))
)
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
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: