cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tomw_1996
Regular Visitor

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):

 

chair30
couch400
table200
table150
chair11
desk100
chair60
couch180
chair100

 

If I would do a normal norm.dist calculation it wouldn't do any good since there are different products. That's why I want to group the calculation based on the product column. Each item should be calculated on it's own median and mean prices, and not on all the prices. It's btw big data with around 1K different items.

 

Any idea? Thanks in advance!

1 ACCEPTED SOLUTION

Aha, @tomw_1996  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_0-1675436108002.png

View solution in original post

6 REPLIES 6
FreemanZ
Community Champion
Community Champion

hi @tomw_1996 

try to plot a table visual with the product column and measures like:

 
mean = MEAN(TableName[Price])
median = MEDIAN(TableName[Price])
 
Or?

I would like to have the data in a calculated column, so that won't work. Besides that I dont want the distribution over all the prices but the distribution for each product within it's own prices.

hi @tomw_1996 

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 everything on all the data, while you want to know the mean/median/stddev based on the items prices. Example;

 

itempricestddev
chair30A
couch400B
table200C
table150C
chair11A
desk100D
chair60A
couch180B
chair100A

The problem is that it calculates it over all the data ... I want to group the values based on the items. Example:

 

item pricemedmeanstd.dev
chair304550.2533.62
couch400290290110
table20017517525
table15017517525
chair114550.2533.62
desk100nullnullnull
chair604550.2533.62
couch180290290110
chair1004550.2533.62

 

The endgoal is to filter out outliers automatically within the report.

Aha, @tomw_1996  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_0-1675436108002.png

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors